none
Can't get my vlookup to work with my form! RRS feed

  • Question

  • Hi,

    I am creating a simple form that looks up the first few letters of a name and an associated County they are employed in.

    Here is my form (which I would like to keep as is):

    Here is my code:

    Dim Vlook1 As Variant
    Dim Vlook2 As Variant
    Dim Name As String
    Dim County As String
    Sub MultipleFunc()
    On Error Resume Next
    Err.Clear
    If Err.Number = 0 Then
    Range("G6") = Vlook1 + Vlook2
    Else
    MsgBox ("No value found")
    End If
    End Sub
    Sub CommandButton1_Click()
    Call MultipleFunc
    End Sub
    Private Sub CommandButton2_Click()
    Unload Me
    End Sub
    Sub TextBox1_Change()
    Vlook1 = Application.WorksheetFunction.VLookup(Left(Name, 12) & "*", Range("B2:E31"), 2, False)
    End Sub
    Sub TextBox2_Change()
    Vlook2 = Application.WorksheetFunction.VLookup(Left(County, 12) & "*", Range("B2:E31"), 2, False)
    End Sub

    I'm only getting "IDID" in my ID field.

    I think my problem is not knowing how to combine the two vlook1 variables and then subsequently displaying the correct ID.

    Can someon show me where I'm going wrong and a suitable and similar alternative, preferably using similar code?

    As you may have guessed I am an amateur, with grammar as well.

    Thanks,

    Tom.


    • Edited by cardinaluk Monday, December 10, 2012 11:12 AM
    Monday, December 10, 2012 11:06 AM

Answers

  • The logic of your code wasn't quite right - you want to find an ID for the combination of the specified name and county; you can't just add the IDs for a match on name and a match on county together.

    Also, you declare and use variables Name and County, but you don't assign a value to them in your code, so they will always be empty strings.

    The code for CommandButton2_Click was correct.


    Regards, Hans Vogelaar

    • Marked as answer by cardinaluk Monday, December 10, 2012 4:11 PM
    Monday, December 10, 2012 3:24 PM

All replies

  • Try replacing the code that you posted with

    Sub CommandButton1_Click()
        Dim r As Long
        Dim m As Long
        Dim f As Boolean
        ' Determine the last row
        m = Range("B" & Rows.Count).End(xlUp).Row
        ' Loop through the rows
        For r = 3 To m
            ' Check if column B matches the name and D the county
            If Range("B" & r) Like Me.TextBox1 & "*" And _
               Range("D" & r) Like Me.TextBox2 & "*" Then
                ' If so, enter ID in column C and exit loop
                Range("G6") = Range("C" & r)
                f = True
                Exit For
            End If
        Next r
        ' If not found, inform user
        If f = False Then
            MsgBox "Not found!", vbExclamation
        End If
    End Sub
    
    Private Sub CommandButton2_Click()
        Unload Me
    End Sub


    Regards, Hans Vogelaar

    Monday, December 10, 2012 12:38 PM
  • Thank you Hans.

    Can you tell me if the code I posted was close? Could you pick it apart and say what could have been used correctly?

    I'm experimenting with forms you see.

    • Edited by cardinaluk Monday, December 10, 2012 1:16 PM
    Monday, December 10, 2012 1:11 PM
  • The logic of your code wasn't quite right - you want to find an ID for the combination of the specified name and county; you can't just add the IDs for a match on name and a match on county together.

    Also, you declare and use variables Name and County, but you don't assign a value to them in your code, so they will always be empty strings.

    The code for CommandButton2_Click was correct.


    Regards, Hans Vogelaar

    • Marked as answer by cardinaluk Monday, December 10, 2012 4:11 PM
    Monday, December 10, 2012 3:24 PM