CODEWITHSUNDEEP
vbaexcelcombobox
Jan Hlade
Jan Hlade

Reputation: 15

Set cell vlookup value based on changing Combobox value and write it to another cell in other worksheet

i am trying to fill a certain cell in excel worksheet ("iNVD) with data from worksheet "database" based on changing combobox. Whene cell A2 in worksheet (database) in chosen by combobox i try to read H2 and write it to worksheet (iNVD). My code and error while buton is pressed enter image description here

Dim rngTblArray As Range

Set nvdsheet = Worksheets("iNVD")
With Worksheets("database")
    'Note when using With statement that .Range and .Cells start with a dot
    Set rngTblArray = .Range(.Cells(2, 8), .Cells(500000, 8))   'One column
End With
'MsgBox rngTblArray.Address(External:=True)  'This line for demo purposes only

nvdsheet.Cells(3, 7) = Application.VLookup(CLng(Me.cbmnaslovnvd.Value), rngTblArray, 2, 0)

Me.vbmnaslovnvd.Value, return "Ulica Jakca 1" for example but CLng(Me.cbmnaslovnvd.Value) = whene I use buttonclick.

The cell in excel "Ulica Jakca 1" is text format cell in datasheet.

Please help me. Thank you

Upvotes: 0

Views: 1429

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27239

You have the right technique, but not the correct understanding of it.

Adjust your code to this:

Dim rngTblArray As Range

Set nvdsheet = Worksheets("iNVD")
With Worksheets("database")
    'Note when using With statement that .Range and .Cells start with a dot
    ' ** set the ENTIRE lookup column reference (A:H)
    Set rngTblArray = .Range(.Cells(2, 2), .Cells(500000, 8))   'One column
End With
'MsgBox rngTblArray.Address(External:=True)  'This line for demo purposes only

' ** CLng will not work with text values (that cannot transform to numbers)
' ** lookup the 8th column, since H is 8 columns from A, where the first lookup value is
nvdsheet.Cells(3, 7) = Application.VLookup(Me.cbmnaslovnvd.Value, rngTblArray, 8, 0)

I made some comments (lines with **), but to understand more of why I made the two changes I did, read up on the VLOOKUP FUNCTION

Upvotes: 2

Related Questions