matching rows between worksheets/books - best practice

[Chris Strug]

Hi,

Something of a newbiw to Excel VBA so please excuse me if this is an obvious
question.

I have two sheets. One contains original data, the other essentially the
same data just ordered and formatted in a different way. Each row is
identified with a unique ID.

I require a function that will loop through each row of the source sheet,
find the matching row (using the ID) on the destination sheet and then do
the required formatting. However I'm unsure how to do the match, I've looked
at the vlookup worksheetfunction but I'm unsure how it handles not being
able to find the ID (i.e. the source row isn't on the destination).

The approach I have taken is:

' Find the last row of source data and assign it to variable.

for icount = 1 to lastrow
'lookup value in destination here
'if found do various things. not applicable to this question
next icount

What is the best practice for looking up the ID's between the two sheets?
Vlookup, find, programming loops, etc.

Any and all advice is gratefully received.

Kind thanks

Chris.

 

[Tom Ogilvy]

Dim rng1 as Range, rng2 as Range
Dim cell1 as Range, cell2 as Range
Dim res as Variant
' set source range
With worksheets("Sheet1")
set rng1 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With
' set range on sheet2
With worksheets("Sheet2")
set rng2 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With

for each cell1 in rng1
res = application.Match(cell1.value,rng2,0)
if not iserror(res) then
set cell2 = rng2(res)
' use cell2 to do your formatting
' for example
cell2.Interior.ColorIndex = 5
end if
Next