0

I'm trying to search for a result based on 2 criteria. "For Each" is much too slow.

This code is much faster. It writes a formula in the cell and then converts it to text. After a few hundred lines, values are still in the cell but they are no longer correct.

I tried manually pasting the =LOOKUP formula. It fetches the correct value, so I assume that it's moving so fast it doesn't have time to find the correct value before it's converted to text.

Sheets("Combined").Select
'put the formula in the first cell
Sheets("Combined").Range(ColumnLetter & "2").Value = "=LOOKUP(2,1/('SheetName'!B:B=Combined!B2)/('SheetName'!A:A=Combined!A2),'SheetName'!C:C)"
'copy the formula all the way down
Sheets("Combined").Range(ColumnLetter & "2").AutoFill Destination:=Range(ColumnLetter & "2:" & ColumnLetter & lastRow)
'convert the result to text
Sheets("Combined").Range(ColumnLetter & "2:" & ColumnLetter & lastRow).Value = Sheets("Combined").Range(ColumnLetter & "2:" & ColumnLetter & lastRow).Value
double-beep
  • 5,031
  • 17
  • 33
  • 41
GymLeaderTalon
  • 1
  • You don't need to `AutoFill` btw: `Sheets("Combined").Range(ColumnLetter & "2:" & ColumnLetter & lastRow).Formula = "=LOOKUP(2,1/('SheetName'!B:B=Combined!B2)/('SheetName'!A:A=Combined!A2),'SheetName'!C:C)"`. – BigBen Sep 27 '22 at 18:54
  • 1
    Why are you referencing entire columns? Just one instance of `=LOOKUP(2,1/('SheetName'!B:B=Combined!B2)/('SheetName'!A:A=Combined!A2),'SheetName'!C:C)` has to process **more than 2 million cells**. So only a few hundred or so copies of this formula will bring Excel to a standstill. That's where your performance issues lie. – Jos Woolley Sep 27 '22 at 19:02
  • @BigBen thank you for the suggestion, I tried using .Formula but it increments all of the formula values, including the reference range which causes an error. Here is a sample of the formula taken from cell 34: =LOOKUP(2,1/('SheetName'!B34:B58250=Combined!B34)/('SheetName'!A34:A58250=Combined!A34),'SheetName'!C34:C58250) – GymLeaderTalon Sep 29 '22 at 18:42
  • Make the row absolute: `B$34:B$58250`, `A$34:A$58250`, and `C$34:C$58250`. – BigBen Sep 29 '22 at 18:43

2 Answers2

0

If you are correct and excel does not have time to complete the search, the following will force a recalculate, and wait until it completes.

Application.Calculate
If Not Application.CalculationState = xlDone Then
  DoEvents
End If

You may not need the first line because when you paste it is most likely making excel recalculate anyway.

Jos makes a good point: this will most likely take a long time if you have hundreds of lines.

https://stackoverflow.com/questions/11277034/wait-until-application-calculate-has-finished

dms292
  • 71
  • 1
  • 9
0

I took the suggestions from @BigBen and @Jos Woolley and combined them, this has resolved my issue. The code gets the correct result and still outperforms my 'For Each' code that I was comparing it to. The 'For Each' takes 8.5 minutes to get through the 30k+ lines and this newly modified code gets through the same amount of data in only 2 minutes, albeit without updating a progress bar, but I'll take the shorter time span. Thank you everyone for your help! Here is the updated code:

Sheets("Combined").Select
'put the formula in all cells
Sheets("Combined").Range(ColumnLetter & "$2:" & ColumnLetter & "$" & lastRow).Formula = "=LOOKUP(2,1/('SheetName'!B$2:B$" & lastRow2 & "=Combined!B2)/('SheetName'!A$2:A$" & lastRow2 & "=Combined!A2),'SheetName'!C$2:C$" & lastRow2 & ")"
'convert the result to text
Sheets("Combined").Range(ColumnLetter & "2:" & ColumnLetter & lastRow).Value = Sheets("Combined").Range(ColumnLetter & "2:" & ColumnLetter & lastRow).Value

For comparison, here is the 'For Each' code I was speed testing this code against:

Sheets("Combined").Select
For Each cell In Range(ColumnLetter & "2:" & ColumnLetter & lastRow)
    cellNumber = Right(cell.Address, Len(cell.Address) - Len("####"))
    MainMenu.numberCompleteLabel.Caption = cellNumber & "/" & lastRow & " complete"
    cell.Value = "=LOOKUP(2,1/('SheetName'!B2:B" & lastRow2 & "=Combined!B" & cellNumber & ")/('SheetName'!A2:A" & lastRow2 & "=Combined!A" & cellNumber & "),'SheetName'!C2:C" & lastRow2 & ")"
    cell.Value = cell.Value
Next cell

Thank you everyone for your help!

GymLeaderTalon
  • 1