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
C
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
--
--
So
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) >
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
nRowIn
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 Facebo
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
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
cRaw
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
-
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 lF
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 Grace,
Here is the latest version, with summary per code and two bugs
removed...
Good luck. Thanks for feedback.
cu
Paul Willekens
'===
Sub CalcTime()
Dim cAZ
Dim cCode
Dim cDatum
Dim cOldAZ
Dim cOldCode
Dim cOldDatum
Dim c
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 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 the
Hi Grace,
Let's start with this... If any changes are needed, just tell me...
Kind regards,
Paul Willekens
'===
Sub CalcTime()
Dim cCode
Dim cDatum
Dim cOldCode
Dim cTime
Dim dDate
Dim lIn
Dim nTime
Dim nPosInOut
Dim nPosSpa
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 centra
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
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 suits
Hi Hugo,
Total width for the 4 first samples seems 3300mm to me, which is
greater than 3000mm... Or do I not understand your explanation well?
Please confirm and correct if necessary...
greetings
Paul Willekens
--
---
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 =
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
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
Sheets("OTHER").Acti
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. F
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 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 impor
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 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 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 Willekens
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 ca
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
Dim
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
Dim
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. Follo
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 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 tric
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 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 I
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 regard
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 = T
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 th
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, 1).Valu
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 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 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 c
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 fun
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 cSh
OK, Amit, create your excel with from row 2 the files, from-paths and
to-paths as specified... If you save that workbook as "c:\xls
\MoveIt.xls" then the following "MoveIt.vbs" will do what you
expect... just run it under windows...
Let me know if you are satisfied...
Greetings
Paul Willekens
'=
Yes, there is a way, and it's name is VBS scripting: it allows you to
grab data from the browser and insert it into your excel sheet...
Greetings
Paul Willekens
--
--
Some important links for excel users:
1. Follow
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
--
-
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
--
--
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
--
--
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 = ActiveCell.
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
--
--
So
51 matches
Mail list logo
