Use Vlookup(?) in VBA forms

[Bandito1]

Hello all,

I got an userform named userform2 that displays Code and Productname in labels.

On this form is a button that passes through this Code and Productname;

Private Sub UserForm_Initialize()

lblGelCodeR.Caption = UserForm2.commonVariable
lblProductNameR.Caption = UserForm2.commonVariable1

This form is named; GelBMRProperties.

Now i would like that i can use the Code on this forum to look into worksheet "Products" and return values that are behind the corresponding Code number.

For example lblGelCodeR.Caption = 1000

In worksheet "Products" in column A it says 1000 and and column B it says 11. I would like to see that "11" back onto my userform.
I thought something with Vlookup but i don't know how to use that in VBA.

Can someone assist me?

 

[steve the fish]

This is how i would do it using vlookup:

x = Application.VLookup(lblGelCodeR.Caption, Sheets("Products").Columns("A:B"), 2, 0)
If Not IsError(x) Then TextBox1.Text = x

You may need to convert a number to a true number so it may fail.

 

[Bandito1]

Hey,

Thanks for your reply. The code doesn't work.

I also tried the follow but it errors as result;

Private Sub UserForm_Initialize()
lblPage.Caption = Application.VLookup(lblGelCodeR.Caption, Sheets("Products").Columns("A:B"), 2, 0)
End Sub

 

[steve the fish]

Probably because its numeric as suggested. Try a little change as so:

myLookup = lblGelCodeR.Caption
If IsNumeric(myLookup) Then myLookup = Val(myLookup)
x = Application.VLookup(myLookup, Sheets("Products").Columns("A:B"), 2, 0)
If Not IsError(x) Then TextBox1.Text = x

 

[Bandito1]

Thanks once again for your reply.

My knowledge of VBA is not that great.

The caption that should change is lblPage.

I changed that in the code but it doesn't return anything.

Private Sub UserForm_Initialize()myLookup = lblGelCodeR.Caption
If IsNumeric(myLookup) Then myLookup = Val(myLookup)
x = Application.VLookup(myLookup, Sheets("Products").Columns("A:B"), 2, 0)
If Not IsError(x) Then lblPage.Caption = x
End Sub

 

[Norie]

When are the controls on the form being populated?

 

[Bandito1]

Hey,

I will redo my question, maybe it's becomes more clear / easier.

I got a userform named "Production Returns". On this is a label called "lblGelCodeR"
lblGelCodeR.caption displays a code like 0001.
On this forum is a link that opens a new userform called "GelBMRProperties".
the link contains the following code;

Private Sub lblInfo_Click()  With GelBMRProperties
      .lblGelCodeR.Caption = Productionreturns.lblGelCodeR.Caption
      .lblProductNameR = Productionreturns.lblProductNameR.Caption
      .Show
   End With
End Sub

The userform "GelBMRProperties" also contains a label named lblGelCodeR. The caption of this label is the same as the caption of lblGelCodeR on the userform "Production Returns".
On this userform i would like to use something like Vlookup.

The caption of lblGelCodeR (for example 0001) corresponds with the worksheet "Products". In this worksheet is in column A the Gelcodes (for example 0001) and in column B the amount of pages of the document.

Hope this makes it more clear

 

[Bandito1]

I tried to follow the following code;

myLookup = lblGelCodeR.Caption
If IsNumeric(myLookup) Then myLookup = Val(myLookup)
x = Application.VLookup(myLookup, Sheets("Products").Columns("A:B"), 2, 0)
If Not IsError(x) Then TextBox1.Text = x

It seems like MyLookup stays empty. Anyone knows why?