3
\$\begingroup\$

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
\$\endgroup\$
2
  • \$\begingroup\$ 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. \$\endgroup\$
    – RubberDuck
    Apr 14 '15 at 16:19
  • \$\begingroup\$ 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. \$\endgroup\$
    – FreeMan
    Apr 14 '15 at 16:29
2
\$\begingroup\$

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

enter image description here

And sheet Data_old

enter image description here

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.

enter image description here

\$\endgroup\$
2
  • \$\begingroup\$ 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! \$\endgroup\$
    – Nico
    Apr 14 '15 at 16:49
  • \$\begingroup\$ You're welcome! Good luck! \$\endgroup\$
    – RubberDuck
    Apr 14 '15 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.