Do Loop Vs For Each

[jlclyde]

I am not as familiar with For each loops. I am trying to write a
macro that will go down a list and return a Concatenation of another
column of every item that matches the original column. For instance
if the item numbers are in col A and customer names are in Col B, I
would like to have all the customers who use item one to be listed as
Cust1, Cust2, Cust3.... and so on. I can do this with a Do until
loop but it takes for ever. I have heard that For Each are much
faster.

Thanks,
Jay

 

[Jim Thomlinson]

One loop will not be distinctly faster than the other... For each might be a
bit better but only marginally from my experience. For Each works on
collections of objects. You can traverse each item in the collection
something like this

dim wks as worksheet 'single worksheet object

for each wks in worksheets 'Worksheets collection
msgbox wks.name
next wks

or
dim rng as range

for each rng in Range("A1:A20")
msgbox rng.address
next rng

If you have the need for speed in what you are doing you would probably be
best to leverage the find function. That would allow you to avoid having to
check the value of every cell. The code is relatively straight forward. RSVP
if you would like to see how it's done...

 

[jlclyde]

One loop will not be distinctly faster than the other... For each might be a
bit better but only marginally from my experience. For Each works on
collections of objects. You can traverse each item in the collection
something like this

dim wks as worksheet 'single worksheet object

for each wks in worksheets 'Worksheets collection
 msgbox wks.name
next wks

or
dim rng as range

for each rng in Range("A1:A20")
 msgbox rng.address
next rng

If you have the need for speed in what you are doing you would probably be
best to leverage the find function. That would allow you to avoid having to
check the value of every cell. The code is relatively straight forward. RSVP
if you would like to see how it's done...
--
HTH...

Jim Thomlinson






- Show quoted text -

I would like to see how the find is done.
I was understanding the Do loop to go by each cell and the for each to
pick out all that are matching the condition and then doing what needs
to be done. What you said make sense.

Thanks,
Jay

 

[Jim Thomlinson]

Sub FindCustomers()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim rng As Range

Set rngToSearch = Range("A:A") 'Change
Set rngFound = rngToSearch.Find(What:="This", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=True) 'looking for "This"

If rngFound Is Nothing Then
MsgBox "This was not found"
Else
strFirstAddress = rngFound.Address
Set rngFoundAll = rngFound
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress

'move 1 column left to B
Set rngFoundAll = rngFoundAll.Offset(0, 1)
For Each rng In rngFoundAll
MsgBox rng.Value
Next rng
End If
End Sub