nexted for next loops
 

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

nexted for next loops
 

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" wrote in message
...
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

nexted for next loops
 

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


"Otto Moehrbach" wrote:

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" wrote in message
...
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

nexted for next loops
 

Ditto. Post back if you need any pointers. Otto
"dr chuck" wrote in message
...
Thanks for your input. I will investigate "find". I am a learn as you go
VBA
programmer.
--
dr chuck


"Otto Moehrbach" wrote:

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" wrote in message
...
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

nexted for next loops
 

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


"dr chuck" wrote:

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