PDA

View Full Version : Solved: WorksheetFunction.Vlookup Different Workbook Reference



YellowLabPro
09-19-2007, 09:45 AM
Error Message receiving:
'Unable to get the Vlookup property of the WorksheetFunction class'
Looking at the value in the activesheet, the table array argument reference is the workbook, sheet referenced in the function below.



For i = 4 To lrwsource
If Cells(i, 23).Value > 40 Then
Cells(i, 9).Value = WorksheetFunction.VLookup(Cells(i, 9), "[TGSItemRecordCreatorMaster.xls]ColorDB!$J$3:$K$198", 2, 0)
End If
Next i

mdmackillop
09-19-2007, 09:52 AM
Try
Application.WorksheetFunction.VLookup

YellowLabPro
09-19-2007, 10:00 AM
Hi Malcom,
Tried it and get the Same error-
I found a post here that discusses the error, but I don't think it pertains to my case. This returns a valid answer in the spreadsheet when done as an excel function. Both the lookup value and the value residing and returning are all in place for testing.
http://www.cpearson.com/excel/CallingWorksheetFunctionsFromVBA.htm

Bob Phillips
09-19-2007, 10:08 AM
Is that workbook closed?

Norie
09-19-2007, 10:10 AM
Doug

The second argument of VLOOKUP, in VBA or on a worksheet, is a range.

Not a string, which is what this is.

"[TGSItemRecordCreatorMaster.xls]ColorDB!$J$3:$K$198",

YellowLabPro
09-19-2007, 10:11 AM
It is open Bob

Bob Phillips
09-19-2007, 10:14 AM
So, does this work



For i = 4 To lrwsource
If Cells(i, 23).Value > 40 Then
Cells(i, 9).Value = WorksheetFunction.VLookup(Cells(i, 9), Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("ColorDB").Range("J3:K198"), 2, 0)
End If
Next i

YellowLabPro
09-19-2007, 10:26 AM
Yes Bob, it sure does.
Thanks....
Now I am thinking I better wrap this in the error trapping I saw on C.Pearson's article....

Norie I saw your point, but could not sort it out... Needed it spelled out.... :help thanks Norie.


Thanks Bob....

Norie
09-19-2007, 10:37 AM
Doug

No problem, just thought I'd point you in the right direction hoping you could figure it out yourself.

That's sometimes a good way to learn.:)

YellowLabPro
09-19-2007, 11:47 AM
Part Duex,

This is all working correctly now, thanks for the assistance. I am curious if a loop result can tie into another loop and take action dependent on something that occurred in the first loop?

The first loop will evaluate the contents in col. N, a secondary color. If there is an error and the lookup function cannot change the value, it resumes on to the next record until finishing the loop.
Once this loop is complete, it will perform the same instructions on col. M, the primary color column. If there was an error in the first loop on say cell N8, can I instruct the loop to skip its adjacent cell M8 in the second loop?

I cannot combine the loops, it evaluates the length of the record which is the item that triggers the code to perform this instruction. So, if the value is greater than 40, abbreviate the second color, if after the loop finishes and the record remains greater than 40, then abbreviate the first color. So....


Sub LenHelper()
Dim ws As Worksheet
Dim i As Long, j As Long, lrwsource As Long, iStatus As Long

lrwsource = 9

For i = 4 To lrwsource
On Error Resume Next
If Cells(i, 24).Value > 40 Then
Cells(i, 14).Value = WorksheetFunction.VLookup(Cells(i, 14), _
Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("ColorDB").Range("K3:L500"), 2, 0)
End If
Err.Clear
Next i
Application.ActiveSheet.Calculate
For i = 4 To lrwsource
On Error Resume Next
If Cells(i, 24).Value > 40 Then
Cells(i, 13).Value = WorksheetFunction.VLookup(Cells(i, 13), _
Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("ColorDB").Range("K3:L500"), 2, 0)
End If
Err.Clear
Next i
Application.ActiveSheet.Calculate
End Sub

Bob Phillips
09-19-2007, 12:02 PM
Why don't you put both actions in one loop, and not do the second action if the first fails?

YellowLabPro
09-19-2007, 12:14 PM
ok.... i will buy that... let me ponder on how to accomplish that

YellowLabPro
09-19-2007, 03:57 PM
Here is my updated code. This takes into account whether the first condition will error if it has a value to compare against, ensuring it does not error if the cell is empty.

If it does error, it skips the next statement and resumes to the next i.

Is this how you would recommend handling this scenario?


Sub LenHelper2()
Dim ws As Worksheet
Dim i As Long, lrwsource As Long, iStatus As Long

lrwsource = 9

For i = 4 To lrwsource
On Error Resume Next

If Cells(i, "W").Value > 40 And Not IsEmpty(Cells(i, "N")) Then
Cells(i, "N").Value = WorksheetFunction.VLookup(Cells(i, "N"), _
Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("ColorDB").Range("K3:L500"), 2, 0)
End If

'Update value to ensure accurate evaluation of value in (i,"W")
Application.ActiveSheet.Calculate
If Err.Number <> 0 Then
GoTo Skip:
Else
If Cells(i, "W").Value > 40 Then
Cells(i, "M").Value = WorksheetFunction.VLookup(Cells(i, "M"), _
Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("ColorDB").Range("K3:L500"), 2, 0)
End If
End If
Skip: Err.Clear

Application.ActiveSheet.Calculate
Next i

End Sub

Bob Phillips
09-20-2007, 12:39 AM
Yeah, haven't tested it, but that approach looks good.

I would just make a few cosmetic changes to make it a bit tidier



Sub LenHelper2()
Dim ws As Worksheet
Dim i As Long, lrwsource As Long, iStatus As Long

lrwsource = 9

For i = 4 To lrwsource

With Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("ColorDB")

On Error Resume Next

If Cells(i, "W").Value > 40 And Not IsEmpty(Cells(i, "N")) Then
Cells(i, "N").Value = WorksheetFunction.VLookup(Cells(i, "N"), _
.Range("K3:L500"), 2, 0)
End If

'Update value to ensure accurate evaluation of value in (i,"W")
If Err.Number = 0 Then

Application.ActiveSheet.Calculate

If Cells(i, "W").Value > 40 Then
Cells(i, "M").Value = WorksheetFunction.VLookup(Cells(i, "M"), _
.Range("K3:L500"), 2, 0)
End If
End If
End With

Application.ActiveSheet.Calculate
Next i

On Error GoTo 0
Err.Clear

End Sub

Bob Phillips
09-20-2007, 12:44 AM
Just one thought, if



If Cells(i, "W").Value > 40 And Not IsEmpty(Cells(i, "N")) Then


this fails, should you skip the second test or not?

YellowLabPro
09-20-2007, 01:46 AM
Tidying up the code I could not figure out how to do this w/out the use of the GoTo Skip: and the Err.Number<>0, thanks- this is perfect.


Just one thought, if

If Cells(i, "W").Value > 40 And Not IsEmpty(Cells(i, "N")) Then

this fails, should you skip the second test or not?
From my understanding of the way I wrote this and your question, Yes.
But, not being sure if there is a third option here, so I will lay it out in case you see another scenario I don't.
Not every record will have a second color, the function will error if the first condition test cell is empty; no second color.
If the record's first color is not found, it will error & skip the next IF condition, which is my intent.
Am I correct in my code here?

Bob Phillips
09-20-2007, 02:14 AM
You would need to test what you have as I haven't done so, but I don't think so.

To cater for that, I think this is more appropriate (again needs to be tested)



Sub LenHelper2()
Dim ws As Worksheet
Dim i As Long, lrwsource As Long, iStatus As Long
Dim fPass As Boolean

lrwsource = 9

For i = 4 To lrwsource

fPass = False

With Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("ColorDB")

On Error Resume Next

If Cells(i, "W").Value > 40 And Not IsEmpty(Cells(i, "N")) Then
Cells(i, "N").Value = _
WorksheetFunction.VLookup(Cells(i, "N"), .Range("K3:L500"), 2, 0)
Else
fPass = True
End If

'Update value to ensure accurate evaluation of value in (i,"W")
If Err.Number = 0 And Not fPass Then

Application.ActiveSheet.Calculate

If Cells(i, "W").Value > 40 Then
Cells(i, "M").Value = WorksheetFunction.VLookup(Cells(i, "M"), _
.Range("K3:L500"), 2, 0)
End If
End If
End With

Application.ActiveSheet.Calculate
Next i

On Error GoTo 0
Err.Clear

End Sub

YellowLabPro
09-20-2007, 02:32 AM
By adding the fPass, are you forcing it into one of the two conditions; not allowing for other possible unforeseen conditions to arise?

Bob Phillips
09-20-2007, 02:34 AM
I don't think so, I am just checking whether the initial condition is met or not as well as checking whether it errors.

YellowLabPro
09-20-2007, 02:39 AM
Tested: Works.
Thank you for adding the final touches here... very helpful and appreciated :)

Bob Phillips
09-20-2007, 02:48 AM
Great. It was easier for you to do the testing than for me to try and setup a testbed that reflected what I thought you had. And your code was nice and neat enough to assimilate very easily.