Solved: WorksheetFunction.Vlookup Different Workbook Reference

[YellowLabPro]

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.


[VBA]
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
[/VBA]

[mdmackillop]

Try
Application.WorksheetFunction.VLookup

[YellowLabPro]

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/Callin...onsFromVBA.htm

[Bob Phillips]

Is that workbook closed?

[Norie]

Doug

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

Not a string, which is what this is.
[vba]
"[TGSItemRecordCreatorMaster.xls]ColorDB!$J$3:$K$198",
[/vba]

[YellowLabPro]

It is open Bob

[Bob Phillips]

So, does this work

[vba]

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
[/vba]

[YellowLabPro]

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.... thanks Norie.


Thanks Bob....

[Norie]

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]

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....

[vba]
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
[/vba]

[Bob Phillips]

Why don't you put both actions in one loop, and not do the second action if the first fails?

[YellowLabPro]

ok.... i will buy that... let me ponder on how to accomplish that

[YellowLabPro]

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?

[vba]
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
[/vba]

[Bob Phillips]

Yeah, haven't tested it, but that approach looks good.

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

[vba]

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
[/vba]

[Bob Phillips]

Just one thought, if

[vba]

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

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

[YellowLabPro]

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
[vba]
If Cells(i, "W").Value > 40 And Not IsEmpty(Cells(i, "N")) Then
[/vba]
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]

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)

[vba]

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
[/vba]

[YellowLabPro]

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]

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]

Tested: Works.
Thank you for adding the final touches here... very helpful and appreciated