Unable to get the LOOKUP property of the WorkSheetFunction class

[Tango7]

This is my second post, and this time I come with a question. When I run the macro attached below, the first LOOKUP function will work fine, if the second one is not in the program. However, when I add the second one, I get the error Unable to get the LOOKUP property of the WorkSheetFunction class.

I am a VBA novice, and I appreciate any help that I can get.

Thanks.

Sub Auto()
Indexer = 0
Iterate = 1
Row = 3
RSIchan = DDEInitiate("RSLINX", "O2X1")
Do While Iterate < 2001

TempCarrierLot = DDERequest(RSIchan, "gCarrier" & "[" & Indexer & "]" & ".LotId")

TempCarrierStatus = DDERequest(RSIchan, "gCarrier" & "[" & Indexer & "]" & ".Status")

TempCarrierNextSta = DDERequest(RSIchan, "gCarrier" & "[" & Indexer & "]" & ".NextStation")

Cells(100, 1).Value = TempCarrierStatus
Cells(100, 2).Value = TempCarrierNextSta

Stat = Sheet1.Application.WorksheetFunction.Lookup(Cells(100, 1).Value, Range("AA2:AA6"), Range("AB2:AB6"))

NextSta = Sheet1.Application.WorksheetFunction.Lookup(Cells(100, 2).Value, Range("AE2:AE31"), Range("AF2:AF31"))
Cells(Row, 1).Value = Indexer + 410000

Cells(Row, 2).Value = TempCarrierLot
Cells(Row, 3).Value = Stat
Cells(Row, 4).Value = NextSta
Row = Row + 1
Indexer = Indexer + 1
Iterate = Iterate + 1
If Row > 49 Then
ActiveWindow.SmallScroll Down:=1
End If
Loop
End Sub

 

[Tango7]

Found the answer

For some reason VBA didn't like the reference to the Cells(100,2).value that I used in the second lookup. When I changed that to the Variant the lookup worked perfectly.

Variants data types and boolean operations still don't seem to work right, which is why I tend to use the Cells(x,y).Value syntax.