CODEWITHSUNDEEP
formsvbaexcelvlookup
jamesc100
jamesc100

Reputation: 137

Error 1004 - Vlookup in vba - Unable to get the Vlookup property of the WorksheetFunction class

I've browsed the various questions already asked with this issue other users have faced and none of the solutions seem to fix the error code coming up.

I have a form which prompts the user for a reference number - they input this into the text field and then press OK

'OK button from form1
    Public Sub CommandButton1_Click()
    refInput = refTextBox.Value
    InputRef.Hide
    ExportShipForm.Show
    End Sub

Once this has been pressed, the next form appears which I would like to be populated with data based on the reference number input on the first form. I have an update button which will update the "labels" on the form to show the data - this is where I am getting an error. The first label to update is through a Vlookup:

Below the users clicks the update button the 2nd form:

Public Sub btnUpdate_Click()
Call ICS_Caption

lbl_ICS.Caption = Label_ICS
End Sub

This calls a function below:

Public Sub ICS_Caption()
Dim ws1 As Worksheet

refInput = InputRef.refTextBox.Value
Set ws1 = Worksheets("MACRO")

dataRef = Worksheets("Shipping Data").Range("A:K")
Label_ICS = WorksheetFunction.VLookup(refInput, dataRef, 7, False)
End Sub

The error continues to come up each time - I have ran the vlookup manually in a cell outside of VBA and it works fine. I have typed the range in the Vlookup whilst also using named ranges but each variation shows the same error.

Eventually, I would want the label on form 2 to update with the result of the Vlookup.

Any ideas?

Upvotes: 1

Views: 7277

Answers (2)

DragonSamu
DragonSamu

Reputation: 1163

You need to Dim dataRef as Range and then Set it.

See code Below:

Dim DataRef as Range
Set dataRef = Worksheets("Shipping Data").Range("A:K")

Just like a Workbook or Worksheet you need to Set the Range

Just as Grade 'Eh' Bacon suggest in comments its always best to Dim every reference.
The best way to do so is to put Option Explicit all the way at the top of your code. This forces you to define everything which helps it preventing mistakes/typo's etc.

Update edit:

The problem was you are looking for a Reference number in your Sheet (thus an Integer) but refInput is set as a String this conflicts and .VLookup throws an error because it can't find a match.

I have reworked your code:

Your sub is now a function which returns the .Caption String

Function ICS_Caption(refInput As Integer)
    Dim dataRef As Range
    Set dataRef = Worksheets("Shipping Data").Range("A:K")
    ICS_Caption = WorksheetFunction.VLookup(refInput, dataRef, 7, False)
End Function

The update Button calls your Function and provides the data:

Public Sub btnUpdate_Click()
    lbl_ICS.Caption = ICS_Caption(InputRef.refTextBox.Value)
End Sub

By using a Function you can provide the Integer value and get a return value back without the need of having Global Strings or Integers.

Which would have been your next obstacle as you can only transfer Variables between Modules/Userforms by using a Global Variable.

I would even advice to directly use the function in the Initialize Event of your 2nd Userform to load the data before the Userform shows this is more user friendly then needing to provide data and then still needing to push an update button.

Upvotes: 1

Ben
Ben

Reputation: 355

  1. Verify that you have no missing libraries in VBA IDE > Tools > References

  2. Try using a worksheet cell as the place to store and retrieve refTextBox.Value, rather than refInput (which I assume is a global variable):

    Public Sub CommandButton1_Click()
...
Worksheets("Shipping Data").Range($M$1).Value=refTextBox.Value  
End Sub

Public Sub ICS_Caption()
Dim refInput as Long'My assumption
...
refInput=Worksheets("Shipping Data").Range($M$1).Value
...
End Sub

  3. Make sure you have Option Explicit at the top of all of your code windows.

Upvotes: 0

Related Questions