Vlookup VBA Variable

[Partjob]

Dear all
I have the following code, I am trying to check if there is a value above 0 through the lookup.

Dim a As String
For Each cCont In Me.Controls
    If TypeName(cCont) = "Label" Then
    a = cCont.Caption
    End If
b = Application.WorksheetFunction.VLookup(a, Sheet1.Range("C4:I24"), 7, False)
If b > 0 Then
MsgBox "You have management time in the quote" & vbCrLf & "Please remove and rerun", _
vbCritical, "CHECK"
UserForm.Hide
Exit Sub
 End If
    Next cCont

In case it is not obvious what I doing. I have a UserForm active and am using the labels in the form as the lookup value (variable a)
I pass this to the lookup but get an error.
Unable to get the Vlookup property of the WorksheetFuction class.
I have looked through various forums on line and I still think I have this correct but it not working.
If I remove the variable element and just put the value instead of the variable it works.
When I stepin I can see the value of "a" is correct
What am I doing wrong?

thanks a lot

 

[Partjob]

Hi

Still struggling with this and could really do with some guidance.

thanks

 

[hilyete]

Try changing your sytax to:

Application.WorksheetFunction.VLookup(a, Worksheets("Sheet1").Range("C4:I24"), 7, False)

and make sure that you're really looking on sheet 1.

 

[Partjob]

Thanks for your reply
I am definatly looking at Sheet1.
If I change the variable to the actual lookup value I can get a result.
I am sure it has something to do with the variable I am just not sure what.
I have declared it as a string.
when I step in I can see the correct value.
I still get the error mentioned above.
Sheet1 is actually called "Labour Values"

Thanks

 

[hilyete]

I took your code and modified it for my own data, created a userform with 4 labels on it and ran your code. In Column A I have a list of Label (Label1 to Label4), randomly listed. In column B, I have either a 1 or a 0. In each case, the code found the 0 and gave the alert message. However, as you have your code, it will hide the userform after finding the first 0, before checking all of the cCont variables. I dont know if that's what you intended. It will also error out if you have a label that doesn't have any value assigned, so I added the error trap. In each case it found the 0 value in column B associated with Labelx and gave the alert message. I don't know why it isn't working on your sheet unless you do not have the data laid out correctly. remember you Label names will have to be in first column(to the left).. other than that I don't know why.

Private Sub CommandButton1_Click()
Dim a As String
Dim b As Integer

For Each cCont In Me.Controls
If TypeName(cCont) = "Label" Then
a = cCont.Caption
On Error Resume Next
b = Application.WorksheetFunction.VLookup(a, Sheet1.Range("A1:B10"), 2, False)
MsgBox b
If b > 0 Then
MsgBox "You have management time in the quote" & vbCrLf & "Please remove and rerun", _
vbCritical, "CHECK"
'UserForm1.Hide
'Exit Sub
End If
End If
Next cCont

 

[hilyete]

I took your code and modified it for my own data, created a userform with 4 labels on it and ran your code. In Column A I have a list of Label (Label1 to Label4), randomly listed. In column B, I have either a 1 or a 0. In each case, the code found the 0 and gave the alert message. However, as you have your code, it will hide the userform after finding the first 0, before checking all of the cCont variables. I dont know if that's what you intended. It will also error out if you have a label that doesn't have any value assigned, so I added the error trap. In each case it found the 0 value in column B associated with Labelx and gave the alert message. I don't know why it isn't working on your sheet unless you do not have the data laid out correctly. remember you Label names will have to be in first column(to the left).. other than that I don't know why.

Private Sub CommandButton1_Click()
Dim a As String
Dim b As Integer

For Each cCont In Me.Controls
    If TypeName(cCont) = "Label" Then
    a = cCont.Caption
 On Error Resume Next
 b = Application.WorksheetFunction.VLookup(a, Sheet1.Range("A1:B10"), 2, False)
   MsgBox b
    If b > 0 Then
        MsgBox "You have management time in the quote" & vbCrLf & "Please remove and rerun", _
        vbCritical, "CHECK"
        'UserForm1.Hide
        'Exit Sub
    End If
 End If
Next cCont

End Sub

 

[hilyete]

That probably didn't clarify things much. The way your code is written, it only checks each "label" for one possibility of 0 or not 0. Is that the way your data is listed? (for example)

Label1 0
Label2 5
Label3 0
Label4 0

Or is it possible in your data that Labels are repeated more than once:

Label1 0
Label2 3
Label1 2
Label3 0
Label2 1
Label1 0

The way I adjusted your code probably wasn't what you are trying to achieve, but at any rate, the "Vlookup function" is working for me, so I dont' know what's causing it not to work for you. Maybe try the code below and change the range info to match yours, and see what happens.

Private Sub CommandButton1_Click()
Dim a As String
Dim b As Integer
For Each cCont In Me.Controls
    If TypeName(cCont) = "Label" Then
    a = cCont.Caption
    
   On Error Resume Next
     
     b = Application.WorksheetFunction.VLookup(a, Sheet1.Range("A1:B10"), 2, False)
        If b > 0 Then
        MsgBox "You have management time in the quote" & vbCrLf & "Please remove and rerun", vbCritical, "CHECK"
        UserForm1.Hide
        Exit Sub
    End If
 End If
Next cCont

End Sub

 

[Partjob]

hilyete
Thanks for your time on this, you as good as replicated my UserForm. I do have 4 labels. The list it is checking against is not allowed to have duplicates, so the label caption will only be listed once.

It was my intention to exit the code if any value was above 0, in my case there is no point going further if the user has added management time and then tries to use this function.

I need to have both methods possible, add management time to the quote and this way of spreading the cost over the number of hours used. different methods for different customers. The easy way of cause is not give the ability to add any management time to the quote.

I will give this some more thought. As I have mentioned before the oddest thing for me is I can get the Vlookup to work by useing the lookup value in the formula instead of a variable. Even stranger for me is the fact that when I step in I can see the value of the variable and it is correct.

Anyway thanks again for your time I am not sure you can help me furhter if I get to the bottom of it I will post on here.