CODEWITHSUNDEEP
excelvbavlookup
Wouter Groeneweg
Wouter Groeneweg

Reputation: 53

Vlookup in another sheet

I am currently working on a userform. In this userform, data is entered in textbox4 and data is placed in textbox6 via commandbutton3 based on Vlookup. However, the vlookup must retrieve its data from the worksheet "DB - verzamelformulier" in the range A: B. Currently I get the error message: 424 object required. Can anybody help me with the code?

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("DB - verzamelformulier")

With ws
Texbox6.Formula = "VLookup(TextBox4.Value, DB - verzamelformulier!$A:$B), 2, False)"

End With
End Sub

Upvotes: 1

Views: 106

Answers (2)

Error 1004
Error 1004

Reputation: 8230

Using Vlookup:

Option Explicit

Sub test()

    Dim varResults As Variant

    varResults = Application.VLookup(TextBox4.Value, ThisWorkbook.Worksheets("Db - verzamelformulier").Range("A:B"), 2, False)

    If Not IsError(varResults) Then
        'If there is a results
        TextBox6.Value = varResults
    Else
        'If there is no result
    End If

End Sub

Upvotes: 0

Frank
Frank

Reputation: 71

Interesting approach, but you can't assign formulae to textboxes, only cells. Try out a function like this:

Function VerzamelFormulier(LookUpValue As Variant) As Variant
    Dim WS As Worksheet
    Dim R As Range

    Set WS = ThisWorkbook.Worksheets("DB - verzamelformulier")
    Set R = WS.Range("A:A").Find(LookUpValue, LookIn:=xlValues, Lookat:=xlWhole)
    If R Is Nothing Then
        ' The value wasn't found.
    Else
        ' Return the value from the cell in the same row and column B.
        VerzamelFormulier = WS.Cells(R.Row, 2)
    End If
End Function

Call it on TextBox4's change event so that whenever it's changed TextBox6's value is updated.

Private Sub TextBox4_Change()
    TextBox6.Value = VerzamelFormulier(TextBox4.Value)
End Sub

Upvotes: 1

Related Questions