CODEWITHSUNDEEP
vbaexcelvlookupuserform
Arnel Natividad
Arnel Natividad

Reputation: 3

userform vlookup without using application.vlookup

I am currently trying to create a userform that would call the matching values of column B,C,D with column A value. In order to show value of column B, C and D into textbox2, textbox3 and textbox4 then I have to put the value of column A to textbox1. My data in my sheet is indefinite so as much as possible I don't want to use application.vlookup. I've used a code for this matter that my colleague provided me which is actually working on some of the userforms that I created. However, as I use it now it just won't work.

Private Sub Textbox1_AfterUpdate()

If Textbox1.Value = "" Then

Textbox2 = ""
Textbox3 = ""
Textbox4 = ""
Exit Sub

Else

x = 2
Do Until Sheet2.Cells(x, "A") = Textbox1.Value
If Sheet2.Cells(x, "C").Value = "" Then
Textbox2 = ""
Textbox3 = ""
Textbox4 = ""
Exit Sub
End If

x = x + 1
Loop

Textbox2 = Sheet2.Cells(x, "B")
Textbox3 = Sheet2.Cells(x, "C")
Textbox4 = Sheet2.Cells(x, "D")
End If

End Sub

I hope you can look into this, point out what is wrong and suggest any corrections.

Thank you!

Upvotes: 0

Views: 125

Answers (3)

chris neilsen
chris neilsen

Reputation: 53136

You objections to VLookupare not valid. There is no need to create a name for the column of my lookup nor to redo range of the name as the lookup values

Just do the search on the whole column.

That said, Application.Match is actually more useful in this case

Your code, refactored

Private Sub Textbox1_AfterUpdate()
    Dim x As Variant
    If Textbox1.Value <> vbNullString Then
        With Sheet2
            x = Application.Match(Textbox1.Value, .Columns(1), 0)
            If Not IsError(x) Then
                If .Cells(x, "C").Value <> vbNullString and x >= 2 Then
                    Textbox2 = .Cells(x, "B")
                    Textbox3 = .Cells(x, "C")
                    Textbox4 = .Cells(x, "D")
                    Exit Sub
                End If
            End If
        End With
    End If
    Textbox2 = vbNullString
    Textbox3 = vbNullString
    Textbox4 = vbNullString
End Sub

before adding data to TextBox1

before adding data to TextBox1

after adding data to TextBox1

after adding data to TextBox1

Upvotes: 2

LatifaShi
LatifaShi

Reputation: 440

Try this one :

   Private Sub Textbox1_AfterUpdate()


                Dim ws As Worksheet
                Set ws = ThisWorkbook.Sheets("sheet1")

                toFind = TextBox1.Value
                Set trouve = ws.Columns("A").Find(what:=toFind, LookIn:=xlValues, Lookat:=xlWhole)
                If trouve Is Nothing Then
                    Debug.Print "Not Found ! "
                Else
                    firstAddress = trouve.Address
                    foundRow = trouve.Row

                    TextBox2.Value = ws.Cells(foundRow, 2)
                    TextBox3.Value = ws.Cells(foundRow, 3)
                    TextBox4.Value = ws.Cells(foundRow, 4)

                End If

    End Sub

Upvotes: -1

PatricK
PatricK

Reputation: 6433

Perhaps you can stick with your previous code that uses vlookup, just change that named range a bit and add another named range as marking of top left data.

Say you have this table (offset from A1 on purpose):
sample data

Named range TopLeftData is set to B3. Then the Name Names is set to this formula =OFFSET(TopLeftData,1,0,COUNTA(Sheet1!B:B)-1,2) (you need to change "Sheet1" or had another name pointing to that column), then vlookup using the Names with work fine.

As long as valid data is below TopLeftData and no data above it, the name Names will work dynamically.

Upvotes: 0

Related Questions