Dim RowDates as Range, RDates as Range
Dim rng1 as Range, res as Variant
Dim cell as Range
Dim StrYes as String
With Worksheets("Sheet1")
set RowDates = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with
With Worksheets("Sheet2")
set RDates = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with

for each cell in RowDates
StrYes = cellOffset(0,1).Value
res = application.Match(clng(cell),RDates,0)
if not iserror(res) then
set rng1 = rDates(res)
' use rng1 to process row
end if
Next


--
Regards,
Tom Ogilvy


"andycharger " wrote in message
...
Hi,

I wonder if you can help guys and gals.....

I have 2 spreadsheets. One has about 1000 rows, one has about 28,000
rows.

What I am doing is using a FOR statement to get the next row in the
smaller spreadsheet and then looping through the 28,000 records in the
other spreadsheet to find the matching row using another FOR statement
that I have nested.
When it is found, it does some changes then exits the inner FOR
statement.

Problem is, it takes FOR EVER! (pardon the pun!)

Any ideas as to how I can improve the speed? Here is a scaled down
version of what I am doing!!!

For RowDates = Range("A1").End(xlDown).Row To 2 Step -1

strRef = Cells(RowDates, "A").Value
StrYes = Cells(RowDates, "B").value

For RDates = Range("A1").End(xlDown).Row To 2 Step -1
if Cells(Rdates, "A").Value = strRef then
' do something

exit for
else

end if
Next

Next



Help!!!!


---
Message posted from http://www.ExcelForum.com/