(VBA) Lookup Formula Provides Wrong Result-CodePudding

I'm trying to write a VBA code to search for a result based on 2 criteria, I've tried "For Each" but it's much too slow. So I'm now using this code, which is much faster, it writes a formula in the cell and then converts it to text, but after a few hundred lines, values are still in the cell but they are no longer the correct result. I've tried manually pasting the Lookup formula it fetches the correct value, so I can only assume that it's moving so fast it doesn't have time to find the correct value before it's converted to text. Can anyone assist?

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

CodePudding user response：

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

CodePudding user response：

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!