Unable to get the LOOKUP property of the WorkSheetFunction class

Tango7

Member
Join Date
Nov 2008
Location
Gainesville, GA
Posts
12
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
 
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.
 

Similar Topics

Hi, One of my customers has an old fabric tensile testing machine. The IC # AD7501KN of its controller has malfunctioned. This IC is related to...
Replies
1
Views
62
Hi all, I'm having difficulties trying to connect FactoryTalk View SE Local Station (V13.00) to MS SQL Server Express. I state that they are...
Replies
2
Views
109
Hi, The VersaMax PLC suddenly stop running (no LED on run function) so I'm trying to upload the program. The HMI connected to it says "Host...
Replies
0
Views
117
Hallo, Windows 10 FactoryTalk View ME Station Version 11.00.00.230 (Patch 0) Unable to run application. I used to run application...
Replies
2
Views
238
Hi everyone I'm in a bit of a conundrum, I've been trying to get this HMI on our machine and I am unable to retrieve it. Device Delta Model...
Replies
10
Views
736
Back
Top Bottom