Help with Error 1004 'Unable to get the Vlookup property of the WorksheetFunction Class

omagoodness · Apr 22, 2016

Hello:
The following code is producing the title error. It works fine if all userform controls contain data but not is there are any empty ones. In this case, the first 3 groups of controls contain data and the 4th was left empty. I had thought that the if..then statement would handle this but it hasn't. What is the coding mistake?

Code:

Private Sub cmdcalculate()

   
  'declare the variables
  Dim RecID1 As Integer, RecID2 As Integer, RecID3 As Integer, RecID4 As Integer
  Dim unit1 As String, unit2 As String, unit3 As String, unit4 As String
  Dim Qty1 As Long, Qty2 As Long, Qty3 As Long, Qty4 As Long
   
  'Set the variables
  RecID1 = Me.cboRec1.Column(0)
  unit1 = Me.cboUnit1.Value
  Qty1 = Me.txtSize1.Value
   
  RecID2 = Me.cboRec2.Column(0)
  unit2 = Me.cboUnit2.Value
  Qty2 = Me.txtSize2.Value
   
  RecID3 = Me.cboRec3.Column(0)
  unit3 = Me.cboUnit3.Value
  Qty3 = Me.txtSize3.Value
   
  RecID4 = Me.cboRec4.Column(0)
  unit4 = Me.cboUnit4.Value
  Qty4 = Me.txtSize4.Value
   
  'Set the range
  Set myrange = Sheets("Sheet2").Range("tblRecipes")
   
   
  'Calculate the cost of plate items from recipes
  If Me.txtSize1 > 0 And Me.cboRec1 > 0 And Me.cboUnit1 > 0 Then
  Me.Cost1 = Application.WorksheetFunction.VLookup(RecID1, myrange, 47, 0) * Qty1
  Else
  Me.Cost1.Value = 0
  End If
  If Me.txtSize2 > 0 And Me.cboRec2 > O And Me.cboUnit2 > 0 Then
  Me.Cost2 = Application.WorksheetFunction.VLookup(RecID2, myrange, 47, 0) * Qty2
  Else
  Me.Cost2.Value = 0
  End If
  If Me.txtSize3 > 0 And Me.cboRec3 > 0 And Me.cboUnit3 > 0 Then
  Me.Cost3 = Application.WorksheetFunction.VLookup(RecID3, myrange, 47, 0) * Qty3
  Else
  Me.Cost3.Value = 0
  End If
  If Me.txtSize4 > 0 And Me.cboRec4 > 0 And Me.cboUnit4 > 0 Then
  Me.Cost4 = Application.WorksheetFunction.VLookup(RecID4, myrange, 47, 0) * Qty4 'This is where the error occurs. The code above gets the desired results but the form controls related to this line are empty.  I thought the if..then statement would handle that.
  Else
  Me.Cost4.Value = 0
  End If
  Me.txtTotalCost.Value = Val(Cost1) + Val(Cost2) + Val(Cost3) + Val(Cost4)
   
  End Sub

Any help would be appreciated. Thank you.

JoeMo · Apr 22, 2016

Most likely, the lookup is returning an error - can't find the lookup value (RecID4) in the lookup range (myrange).
What is the value of RecID4 when you get the error?

omagoodness · Apr 22, 2016

JoeMo said:
Most likely, the lookup is returning an error - can't find the lookup value (RecID4) in the lookup range (myrange).
What is the value of RecID4 when you get the error?

It returns the value of 0 which is what it should return because the form field were left empty. How else can I account for empty controls?

JoeMo · Apr 22, 2016

omagoodness said:
It returns the value of 0 which is what it should return because the form field were left empty. How else can I account for empty controls?

Maybe:

If Me.txtSize4 > 0 And Me.cboRec4 > 0 And Me.cboUnit4 > 0 And RecID4 <> 0 Then

omagoodness · Apr 24, 2016

JoeMo said:
Maybe:

If Me.txtSize4 > 0 And Me.cboRec4 > 0 And Me.cboUnit4 > 0 And RecID4 <> 0 Then

I tried this suggestion but no luck. I checked to be sure that all naming was correct and I deleted the code and did it again just in case there was a character that I wasn't seeing but same problem.
Below is a sceenshot of the form. The code is behind the Calculate Cost button. It looks up the cost of each recipe and calculates the total cost of the menu item. The first 3 groups of controls work perfectly and the 4th one works if there is data but the error comes when the controls are left blank. However, I can leave any of the others blank with no error. Any suggestions? I'm at a total loss and very frustrated.

Thought I could add an image but I can't. Sorry. Here is a link to it though https://drive.google.com/file/d/0B-P13OG_AXB0aUtmSW8zVlF5QmM/view?usp=sharing

JoeMo · Apr 24, 2016

Sorry, I don't download from the web. Maybe someone else can help.

omagoodness · Apr 24, 2016

Np Joe. Thanks for your help.

Help with Error 1004 'Unable to get the Vlookup property of the WorksheetFunction Class

omagoodness

Board Regular

Excel Facts

JoeMo

MrExcel MVP

omagoodness

Board Regular

JoeMo

MrExcel MVP

omagoodness

Board Regular

JoeMo

MrExcel MVP

omagoodness

Board Regular

Similar threads

Forum statistics

Share this page

Help with Error 1004 'Unable to get the Vlookup property of the WorksheetFunction Class

omagoodness

Board Regular

Excel Facts

JoeMo

MrExcel MVP

omagoodness

Board Regular

JoeMo

MrExcel MVP

omagoodness

Board Regular

JoeMo

MrExcel MVP

omagoodness

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock