Hi Jonathan,
Could it be a problem that you skipped the quots...
should be
Filename:=strSelectedPath \Run1.xlsx
instead of
Filename:=strSelectedPath \ Run1.xlsx
Kind greetings
Paul Willekens
--
--
Some
Hi Ronald,
What about running following macro first, and then sorting the
dolumn...
Greetings
Paul Willekens
'===
Sub Omzetten()
Dim nRow
nRow = 1
While Len(Cells(nRow, 1)) 0
If IsNumeric(Cells(nRow, 1).Value) Then
Hi Kevin,
Here the macro...
Feedback please...
Kind Regards,
Paul Willekens
'===
Sub Reformat()
Dim cAddress
Dim cCity
Dim cCompany
Dim cEmail
Dim cName
Dim cPhone
Dim cState
Dim cTitle
Dim nPos
Dim nRowIn
Dim nRowOut
Here it comes...
Feedback please.
Kind regards,
Paul Willekens
'===
Private Sub CommandButton1_Click()
Dim nColOut
Dim nRowIn
Dim nRowOut
nRowIn = 1
nRowOut = 6
nColOut = 7
While Len(Sheets(Sheet1).Cells(nRowIn, 2).Value) 0
Hi Stephen,
No upload in the picture...
Greetings,
Paul Willekens
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our
Hi Praveen
Are we speaking about the same sheet, because in the one you sent I
have 9704 rows, not 7094...
Did you apply the formulae in columns D and E first, for all 121 rows?
If it doesn't function, please send the excel sheet in annex for me to
examen it
Kind regards,
Paul Willekens
Here it is...
Greetings,
Paul Willekens
'===
Sub GetCenters()
Dim cLevel1
Dim cLevel2
Dim cLevel3
Dim cLevel4
Dim cLevel5
Dim cLevel6
Dim cRaw
Dim cTest
Dim lDone
Dim nCenter
Dim nRowIn
Dim nRowOut
nRowIn = 2
nRowOut = 5
Hi DeanL,
Paul gave a great answer, but I am not sure you are going to be happy
with it... Can you please send one of those workbooks aka spreadsheets
and specify clearly what piece of code in the back should have which
modification... I will give you in return a .vbs that iterates through
those
Hi Praveen,
Siti's formulae for column D and E are fine and should be applied
first. But then I suggest to run this macro, because the SUMPRODUCT is
not working for me...
Feedback please.
Kind regards,
Paul Willekens
'===
Sub
Hi Anju,
Of cours, you give too few examples, but at least for the two formats
following macro shoud do the job.
Give some feedback.
Kind regards,
Paul Willekens
'===
Sub PullNumber()
Dim cChar
Dim cContent
Dim cNumber
Dim
Hi Cecilia,
After checking out your excell workbook, IMHO the best is to develop
a .vbs that does both things: grab the specified data in the different
excel files and insert them in the database. Still this problem: if
the end user starts this .vbs, he must have access to the database;
else i
Hi Vinu,
See the solution below... I created a sheet test to see the test
result... In the macro you can (after testing) modify statement
cTest = test
to
cTest = Result Sheet
Good luck.
Please feedback.
Kind greetings
Paul Willekens
Hi Shawn,
Question: are cells in column B always with content, up to the last
used row?
In that case... see below...
Kind regards,
Paul Willekens
'===
Sub GoColumnB()
Dim rngClnB As Range, Cell As Range
Here it comes...
greetings
Paul Willekens
'==
Sub Kleuren()
Dim nRow
nRow = 2
While Len(Cells(nRow, 1).Value) 0
If Cells(nRow, 5).Value = Cells(nRow, 2).Value _
And Cells(nRow, 4).Value = Cells(nRow, 3).Value Then
Here it comes...
Please feedback !
Kind regards,
Paul Willekens
'==
Sub BOcount()
Dim lBO
Dim nBO
Dim nCol
Dim nMax
Dim nRow
'first determine how many dates are registered
nCol = 2
While Len(Cells(1, nCol).Value) 0
nCol =
Hi Chechu,
Why not export that particular range in a .csv file, that would be
stored in a dedicated folder on the network, and imported by Access on
its own time...
You need the macro to export the range in Excel, and a macro in Access
to check for those files and import them...
If that idea
Hi Vivek,
No need to make a .csv
Open the .txt and Excel will ask whether there is a separator or fixed
length... Choose the latter and on the sample you move the vertical
separator lines so to form the columns... then just import and
adjust the column headings...
Go for it !
Greetings
Paul
There are two sides:
a) the user side: if a1:b20 is all the sheet contains, the user could
save his sheet as user.csv in a particular folder
but if the range to import in access is only part of the sheet, maybe
I could develop a little macro to export only that part... You tell
me !
b) the central
Hi Sandeep,
Just try the simple function as follows...
on row 3
=(D3 - C3) * 24 * 60
make sure the editing of the result column is standard (i.e. nothing)
Let me know...
Greetings
Paul Willekens
--
--
Some
Hi Harpreet,
Here it comes... I made a new sheet test to test it, but you can
change that statement in
cCooked = MODIFIED DATA
after testing, of course.
Feedback is welcome.
Kind regards,
Paul Willekens
'===
Sub CookData()
Hi Gaurav,
Did you try my solution? =(D3 - C3) * 24 * 60
What's wrong about that? The result remains numeric, while Ashish's
solution becomes text!
cu
Paul Willekens
--
--
Some important links for excel users:
1.
ok, Prathap, let's start with the following macro: months without
sales but with stock increase will get colored...
is this what you want: highlight it?
greetings
Paul Willekens
'===
Sub StockAnalysis()
Dim lDone
Dim nCol
Hi Java,
If your specifications are vague, then I can only speculate and
simplify...
I assumed first row contains column headings, so I start on row 2 (you
can change that if necessary) in both sheets...
I assumed the numbers to match are located in both sheets in the first
column [if not, you
Hi Janet,
Do I understand you differently from rf1234: you are looking for a
group for OUTLOOK VBA instead of EXCEL VBA, not just for sending mail
from an excel macro ?
I made some macro's in Outlook... shoot your problem, we'll see,
because VBA is not that different.
Greetings
Paul
Hi Hapreet,
I agree with Seema: import with delimiter semicolon and the first
sheet RAW DATA becomes easier.
If it is still very cumbersome for you to convert this Seema-Raw
Data to the Modified data sheet, tell me and I'll make it in the blink
of an eye...
Kind regards,
Paul Willekens
--
Hi Shyam,
I mean the following: I understand what Janet wrote in a different way
than you. I had the impression that you assume that Janet wants
something from Excel, but I think he/she wants VBA macros for Outlook
(not for Excel)...
regards
Paul Willekens
--
Hi JavaOnLine
This method works always and you can insert any specific criteria
without limit...
Good luck. Let me know if it was useful...
Paul Willekens
'===
Sub ESTSearch()
Dim cValue
Dim lDoneA
Dim lDoneB
Dim nNumberA
Hi Rajasekhar
Here it comes...
Enjoy and let me know if it suits you.
Kind regards
Paul Willekens
'===
'module level
Public aDigit(9)
Public aTeenage(8)
Public aTenfold(8)
Public cNumber
Hi Prathap,
Tell us what you want for your analysis... In order to automate, we
need to know what has to be automated...
Till then, kind regards,
Paul Willekens
--
--
Some important links for excel users:
1.
Hi Abdul,
Just send in your spreadsheet, so we can see it with our own eyes and
then work on a solution
Greetings
Paul Willekens
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips
Hi Putta
A couple of lines added to determine the next row to fill...
Greetings
Paul Willekens
'===
Sub Normalize()
Dim cAddress
Dim cBusiness
Dim cCity
Dim cCompany
Dim cContact
Dim cState
Dim lDone
Dim nRow
'first find
Sorry, Putta, but I need to correct my last mail:
In order to be get the next row in Output, first go to that
sheet...
...
Dim lDone
...
'first find the new row in Output
Sheets(Output).Select
nRow = 1
lDone = False
While lDone = False
If Len(Cells(nRow, 1).Value) 1 Then
lDone = True
Hi Mahesh,
First expat means expatriate, a foreigner living and working
abroad... you probable mean expert...
It would be nice if you could append a couple of those text files and
the intended excel workbook, so we can see what you mean exactly...
Waiting to help you out, with kind regards
Hi Abhidha,
Please specify what the result is you look for...
is it jdsdnc versus 2123 versus asdd ... or only extraction of the
number 2123 ?
is it iofruif versus 68732 versus fnw ... or simply 68732 ?
do digits appear separated by alphabetical characters on some
occasions or never?
As soon as
Hi Nike,
Maybe I do not see the problem, but let me focus on the solution...
Just select the columns or range of data cells and specify the cell
properties... special format e.g. mm/dd/ hh:mm or whatever you
preference is...
Good luck
Paul Willekens
--
Hi Archie,
First a question: I suppose your comparison starts with running the
macro, with as ActiveRange the first cell in range 1... Do you know,
at that point, what the first cell of range 2 is? Or is it one of the
tasks of this macro to search for it? I hope you know it and then: how
to pass
Hi Jon
What about following simple macro...
Let me know if it suits you.
Greetings
Paul Willekens
'===
Sub Runner()
Dim aResult(4, 1)
Dim nPos
For nPos = 0 To 4
'Iteration code omitted
aResult(nPos, 0) = Cells(1,
Hi Ramesh,
Supposing that no other split-sequences occur than space, underscore
or hyphen (or a combination of these), then following routine should
do the job...
Enjoy and let me know if it did help you out.
Kind regards
Paul Willekens
Hi Sreejith,
Here is your macro. It should be stored and run from another Workbook,
of course... Just change the number of countries and enter them in the
macro... (arrays start at 0, so 9 countries should make aCountry(8))
Sub Sreejith()
Dim aCountry(2)
Dim cPeriod
Dim cSet1
Dim cSet2
Dim
Hi ScubaGirl
A Function returns a value by assigning a value to its name in one or
more statements of the procedure. The return type of a Function is
always a Variant, unless you specified the type e.g. Public Function
CalcResourceUtilization() as Boolean
If you had some statement within your
Hi Puttu,
Here it comes... Remark how I unmerge merged fields before
grabbing the content... and then I remerge it...
Let me know if this helped you out...
Greetings
Paul Willekens
'===
Sub Normalize()
Dim cAddress
Dim
Would it be okay to have a .vbs script running under WSH capturing
that lock-down time and writing it in your spreadsheet? If yes, then
I'll look further into it... That's because a macro in Excel needs a
trigger to run, but a vbs-script can poll almost anything...
greetings
Paul Willekens
--
go for it... maybe you could elaborate further on error handling and
appropriate messages, but here's the thing...
Sub GaNaar()
Dim cCel
Dim cNewCel
Dim cNewFile
Dim cNewSheet
Dim cSheet
Dim nPos
cCel = ActiveCell.Value
'cell e.g. b2
On Error Resume Next
Range(cCel).Select
cNewCel =
Hi Colvert,
Well, I think you could keep that last purchase order number (PON)
in a (protected) fix cell, e.g. D1
Sub PON()
ActiveCell.Value = Range(D1).Value + 1
Range(D1).Value = ActiveCell.Value
End Sub
Is that what you're up to?
Greetings
Paul Willekens
--
no need of VBA to see the system time in a spreadsheet... function
NOW() should do the job...
unless you would clarify what you mean with at the time of locking
the system... I hope you don't lock the system at all :)
cu
Paul Willekens
--
easy piece...
Sub Kleur()
Dim oCel
For Each oCel In Selection
If oCel.Interior.ColorIndex = 36 Or oCel.Interior.ColorIndex = 38
Then
oCel.Value = N
End If
Next
End Sub
enjoy ...
Paul Willekens
--
--
46 matches
Mail list logo