Application.WorksheetFunction.VLookup 3

[pbt1234]

I am using Application.WorksheetFunction.VLookup to find inventory values for a specified item. When the item is not in the list I get a cryptic message "unable to get the vlookup property of the WorksheetFunction class". How can I deal with this? I thought I could use ISNULL or " = 0" to find these and insert a "0" for the value before things bombed, but neither works. Any help would be appreciated.

Thanks in advance!
pbt1234

 

[SkipVought]

set the lookup result to a VARIANT and then TEST the variant using

Myvar = Application.VLookup(x,y,z,f)
If Not IsError(Myvar) Then

End If

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[pbt1234]

I tried adding what you asked and still get the error message . . . Any more ideas?

Thanks!
pbt1234

Private Sub Newday_click()
Dim strLookUpValue As String
Dim strAnswer As String
Dim rngLookUpRange As Range
Dim intLookUpRow As Integer
Dim myvar As Long

   Do While intLookUpRow < 138
      Set rngLookUpRange = Worksheets("Inventory").Range("A2:D1000")
      strLookUpValue = Worksheets("OPSC").Range("B" & (4 + (7 * intLookUpRow))).Value
           
      myvar = Application.WorksheetFunction.VLookup(strLookUpValue, rngLookUpRange, 4, False)
        
      If Not IsError(myvar) Then
         Sheets("OPSC").Range("E" & (4 + (7 * intLookUpRow))).Value = _
            (Application.WorksheetFunction.VLookup(strLookUpValue, rngLookUpRange, 4, False)) / 1000
      Else
         Sheets("OPSC").Range("E" & (4 + (7 * intLookUpRow))).Value = 0
      End If
            
      intLookUpRow = intLookUpRow + 1
   
   Loop

End Sub

 

[pbt1234]

Sorry Skipvought I did not see the variant part . . . Changed as seen below and still get same error.

Thanks again!
pbt1234

Private Sub Newday_click()
Dim strLookUpValue As String
Dim strAnswer As String
Dim rngLookUpRange As Range
Dim intLookUpRow As Integer
Dim myvar As Variant

   Do While intLookUpRow < 138
      Set rngLookUpRange = Worksheets("Inventory").Range("A2:D1000")
      strLookUpValue = Worksheets("OPSC").Range("B" & (4 + (7 * intLookUpRow))).Value
           
      myvar = Application.WorksheetFunction.VLookup(strLookUpValue, rngLookUpRange, 4, False)
        
      If Not IsError(myvar) Then
         Sheets("OPSC").Range("E" & (4 + (7 * intLookUpRow))).Value = _
            (Application.WorksheetFunction.VLookup(strLookUpValue, rngLookUpRange, 4, False)) / 1000
      Else
         Sheets("OPSC").Range("E" & (4 + (7 * intLookUpRow))).Value = 0
      End If
            
      intLookUpRow = intLookUpRow + 1
   
   Loop

End Sub

 

[xlbo]

rather than testing the result, try testing the function

if (iserror(Application.WorksheetFunction.VLookup(strLookUpValue, rngLookUpRange, 4, False)) then
'there is an error
Else
'no error
End If

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[pbt1234]

Tried that this morning, still same message . . . .

Thanks again - will keep trying.

pbt1234

Private Sub Newday_click()
Dim strLookUpValue As String
Dim strAnswer As String
Dim rngLookUpRange As Range
Dim intLookUpRow As Integer

   Do While intLookUpRow < 138
      Set rngLookUpRange = Worksheets("Inventory").Range("A2:D1000")
      strLookUpValue = Worksheets("OPSC").Range("B" & (4 + (7 * intLookUpRow))).Value
           
      If (IsError(Application.WorksheetFunction.VLookup(strLookUpValue, rngLookUpRange, 4, False))) Then
         Sheets("OPSC").Range("E" & (4 + (7 * intLookUpRow))).Value = 0
      Else
         Sheets("OPSC").Range("E" & (4 + (7 * intLookUpRow))).Value = _
            (Application.WorksheetFunction.VLookup(strLookUpValue, rngLookUpRange, 4, False)) / 1000
      End If
            
      intLookUpRow = intLookUpRow + 1
   
   Loop

End Sub

 

[SkipVought]

as I previously inplied loose WorksheetFunction...

      myvar = Application.VLookup(strLookUpValue, rngLookUpRange, 4, False)

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[xlbo]

Ok then - some slightly more sophisticated error trapping then

on error resume next [COLOR=green]'stop errors interrupting code / turns off auto error handling[/color]
myVar = WorksheetFunction.VLookup(strLookUpValue, rngLookUpRange, 4, False)[COLOR=green]'set vlookup[/color]
errnum = Err [COLOR=green]'obtain error number generated[/color]    
    On Error GoTo 0 [COLOR=green]'reset error handling to be back on again [/color]
        Select Case errnum
          Case 0 [COLOR=green]'no error[/color]
           Sheets("OPSC").Range("E" & (4 + (7 * intLookUpRow))).Value = myVar / 1000
          Case else [COLOR=green]' error generated[/color]
           Sheets("OPSC").Range("E" & (4 + (7 * intLookUpRow))).Value = 0
        End Select

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[pbt1234]

SkipVought - -

Did not see that you had dropped the WorksheetFunction porition. Once I did that worked like a champ!!!! Thanks so much to all of you for helping with this problem. I have NEVER been here with a question that someone has not been able to help with.

Thanks again!
pbt1234

 

[SkipVought]

pbt,

Use one or the other -- not both

Application

WorksheetFunction

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]