# Comparing cells of two Worksheets

With the following Code I try to compare the content of two Worksheets. The first column contains a key that is unique and the following columns contain data that I want to compare (My data sets contain around 7000 rows and up to 40 columns).

My question is if there is a way to make the code run faster. Since the program has to find the key first, it takes longer and longer the further down the row with the key is on the worksheet. A second thing I noticed is that the code generally runs a lot faster with Excel 2007 than 2013. Any ideas why?

Count = 1
LastRowNew= Sheets("Data_new").Cells(Rows.Count, "A").End(xlUp).Row
LastRowOld = Sheets("Data_old").Cells(Rows.Count, "A").End(xlUp).Row
LastColumn = Sheets("Data_old").UsedRange.Columns.Count
For i = 1 To LastRowOld
Set rngA = Sheets("Data_new").Range(Sheets("Data_new").Cells(1, 1), Sheets("Data_new").Cells(LastRowNew, 1)).Find(Sheets("Data_old").Cells(i, 1), lookat:=xlWhole)
If Not rngA Is Nothing Then
Count = Count + 1
Sheets("Comparison").Cells(Count, 1).Value = Sheets("Data_old").Cells(i, 1)
For j = 2 To LastColumn
If Sheets("Data_old").Cells(i, j).Value <> Sheets("Data_new").Cells(rngA.Row, j).Value Then
Sheets("Comparison").Cells(Count, j).Value = "NOK"
Else
Sheets("Comparison").Cells(Count, j).Value = "OK"
End If
Next j
Else
Count = Count + 1
Sheets("Comparison").Cells(Count, 1).Value = Sheets("Data_old").Cells(i, 1)
Sheets("Comparison").Cells(Count, 2).Value = "Doesn't exist in old system"
End If
Application.StatusBar = "Comparing entries " & Format(i / LastRowOld, "0 %") & "..."
Next i

• What are you getting here that can't simply be accomplished with regular ol' Excel formulas? At a glance, I see no reason this couldn't be done with a combination of VLookup and IF. Apr 14 '15 at 16:19
• It's hard to be sure, since the full code isn't there, but you do have Application.ScreenUpdating=False set at the beginning, then reset to True at the end of the routine, right? Try setting Application.StatusBar= only If i mod 10 = 0, that will help reduce screen refresh time. Apr 14 '15 at 16:29

The short answer is don't use VBA at all. Use native Excel formulas. We like to code, so it's natural that we all too often reach for the IDE when we should be heading to the formula bar.

Consider sheet Data_new

And sheet Data_old

You can use a VLookup from Data_old to find the corresponding values in DataNew.

=VLOOKUP(Data_new!$A2,Data_new!$A:$B,2,FALSE)  And then wrap the lookup in an IF formula to populate the final result. =IF(B2 = VLOOKUP(Data_new!$A2,Data_new!$A:$B,2,FALSE),"OK","NOK")


resulting in the new & improved sheet Data_old.

• You're right I never considered going for formulas. The thing is that my tool has prepared the data beforehand to get it into that format. I dont want the user to create formulas by himself. So I will try if I can create those with VBA code and see if that runs faster than manually comparing cells. Thank you!
– Nico
Apr 14 '15 at 16:49
• You're welcome! Good luck! Apr 14 '15 at 16:53