nexted for next loops

[dr chuck]

I am interested in comparing the data in 2 ranges. I want to loop through
each cell in the first loop one at a time and compare it to the data in the
second loop. When the values in both cells are equal then i would like to
copy associated data from the column of the first range to the column of the
second range.

Dim mp As String
Dim frmt As String

For Each c In Worksheets("map check").Range("s4:at4").cells

Let mp = c.Value

For Each d In Worksheets("format").Range("g1:ah1").cells
Let frmt = d.Value

If mp = frmt Then

Windows("PanelSelect.xls").Activate
Sheets("Map Check").Select

With ActiveSheet
.Range(.cells(4, ActiveCell.Column), _
.cells(20, ActiveCell.Column)).Select
End With

Selection.Copy

Windows("PanelSelect.xls").Activate
Sheets("format").Select

Selection.PasteSpecial paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

End If
Next d
Next c


any help is greatly appreciated?
suggestions on other methods to accomplish my goal.
dr chuck

 

[Otto Moehrbach]

That's a lot of looping. I would loop through the first range, then use
Find to search the second range for the value from the first range. If it
is found, do your thing. That's much faster than looping through the second
range for each entry in the first range. HTH Otto

 

[dr chuck]

Thanks for your input. I will investigate "find". I am a learn as you go VBA
programmer.

 

[Otto Moehrbach]

Ditto. Post back if you need any pointers. Otto

Thanks for your input. I will investigate "find". I am a learn as you go
VBA
programmer.

 

[Bill Pfister]

Another method is to load the second list into a collection. This way,
you'll only traverse the list once, and you can do quick checks (relative to
the "find" method) against whether the item from the first list exists in the
collection.

Let me know if youwould like more direction.

Bill