CODEWITHSUNDEEP
excelvba
Austin
Austin

Reputation: 7

set cell value from combobox vlookup

lookup by combobox and return 2nd column value works

Private Sub ComboBox1_Change()
    Dim x As Variant   
    x = Me.ComboBox1.Value
    myLookupValue = x
    Set myTableArray = Worksheets("material pricing").Range("matprice")
    myVLookupResult = Application.WorksheetFunction.VLookup(x, myTableArray.Value, 2, False)
    MsgBox "Product " & x & " is " & Format(myVLookupResult, "#,##0")
    End Sub

change returned value = textbox14 gives an error

Private Sub CommandButton4_Click()
Worksheets("material pricing").Range(myVLookupResult).Value = TextBox14.Value
End Sub

Upvotes: 0

Views: 47

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

myVLookupResult is local to the UserForm_Initialize() procedure: it's defined there, exists there, and nowhere else. Once execution leaves the procedure's scope, the local variable ceases to exist: it's gone, and its value is inaccessible.

Option Explicit not being specified is not helping either.

Private Sub ComboBox1_Change()
    Dim x As Variant   
    x = Me.ComboBox1.Value
    myLookupValue = x
    Set myTableArray = Worksheets("material pricing").Range("matprice")
    myVLookupResult = Application.WorksheetFunction.VLookup(x, myTableArray.Value, 2, False)

Here myVLookupResult isn't the same variable you had in UserForm_Initialize: it's another local variable, local to the ComboBox1_Change procedure, except it's not declared - and the only way this is legal, is because Option Explicit being unspecified, is allowing it.

So a local variable named myVLookupResult is created on-the-fly in that procedure, and again dies when End Sub is encountered.

So when the CommandButton4_Click handler runs:

Private Sub CommandButton4_Click()
Worksheets("material pricing").Range(myVLookupResult).Value = TextBox14.Value
End Sub

myVLookupResult is yet another new local variable, this time local to the CommandButton4_Click procedure - except it has no value, so it's Variant/Empty, and that isn't a valid Range address, so the error you're getting is run-time error 1004: Worksheet.Range(Empty) isn't legal.

Put Option Explicit at the top of the module (and EVERY module you ever write!). That will prevent VBA from successfully compiling code that doesn't declare its variables (which prevents accidental typos!).

Then, move the declaration of myVLookupResult to module-level, like this:

Option Explicit
Private myVLookupResult As Long

Now, that fixes the Variant/Empty problem... but doesn't make the Range call any more legal.

Say the variable contains 42.

Worksheets("material pricing").Range(42).Value = TextBox14.Value

I'm not sure what specific cell you're trying to assign here, but that 42 needs to be a valid cell reference. Say you want to write to cell F42, you could concatenate the 42 with "F", like this:

Worksheets("material pricing").Range("F" & myVLookupResult).Value = TextBox14.Value

And that should work.

Upvotes: 1

Related Questions