Excel VBA Userform - VLookup error

Question

Hi,

I've designed a VBA UserForm.  I want several things to happen once the name is chosen from the combobox.

• Once the "ComboName" is selected "TxtProvider" is filled via a VLookup; this is working ok.
• Then I want information from the previous record under that name (the record comes from a date-sorted Query table "LatestData" which removes duplicates) to appear in a section of the Userform (to remind the user what the last scores were). This is when the errors start (Run-time error 13: Type mismatch).  "PrevDate" is a date/number but all other .text will be text based (Red, Amber, Green or general comments).  The Watch window states the following:

Watch:         Application.VLookup(PrevName.Text, Range("LatestData"), 36, False) : Error 2042 : Variant/Error : Data_UF.ComboName_AfterUpdate

Watch :        PrevDate.Text : "" : String : Data_UF.ComboName_AfterUpdate

Sorry, as a newbie this means absolutely nothing to me!  Here is the rest of the code...

---

Private Sub ComboName_AfterUpdate()

With Data_UF

'Once ComboName is selected from drop-down look up the provider name in table named "Lookup" and enter it into the Userform automatically.

TxtProvider.Text = Application.VLookup(ComboName.Text, Range("Lookup"), 2, False) 'This works

'Once ComboName is selected, complete the PrevName text box in the section marked "Previous Score".

PrevName.Text = ComboName.Text 'This works

'Once ComboName completed in the "Previous score" section, complete the other text boxes to show the previous record.

        PrevDate.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 36, False) 'date format is yyyy/mm/dd

        PrevContract.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 38, False) 'format was a tickbox so query reads true/false

        PrevPlacements.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 37, False) 'format was a tickbox so query reads true/false

        PrevScore1.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 8, False)

        PrevScore2.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 9, False)

        PrevScore3.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 10, False)

        PrevScore4.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 11, False)

        PrevScore5.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 12, False)

        PrevScore6.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 13, False)

        Prevfinance.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 14, False)

        PrevStaffing.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 15, False)

        PrevManagement.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 16, False)

        PrevScore10.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 17, False)

        PrevComments.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 35, False)

        PrevScore.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 29, False)

    End With

End Sub

---

I'm grateful for any help with this. Thanks

Answer 1

Try changing

  PrevDate.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 36, False)

to

  PrevDate.Text = Format(Application.VLookup(PrevName.Text, Range("LatestData"), 36, False), "yyyy/mm")

Answer 2

No - you should stick with False to force an exact match. Are all entries validated (on both the sheet and the userform)? Since you got the "a messagebox says was not found in [workbookname]LatestData!H3:H49." message, that means that an exact match of the userform's entry was not found in that range. So something is wrong with either the entry on the userform, or in H3:H49.....

Answer 3

Make sure tha LatestData is a global name or resides on the active sheet.

For troubleshooting, change your code to this:

Private Sub ComboName_AfterUpdate()

Dim v As Variant

With Data_UF

'Once ComboName is selected from drop-down look up the provider name in table named "Lookup" and enter it into the Userform automatically.

TxtProvider.Text = Application.VLookup(ComboName.Text, Range("Lookup"), 2, False) 'This works

Set v = Range("LatestData").Columns(1)

If IsError(v) Then

    Msgbox "LatestData is not correctly formed"

    Exit Sub

End If

v = Application.Match(PrevName.Text, Range("LatestData").Columns(1), False)

If IsError(v) Then

    Msgbox PrevName.Text & " was not found in " & Range("LatestData").Columns(1).Address(False, False, xlA1, True)

    Exit Sub

End If

'Once ComboName is selected, complete the PrevName text box in the section marked "Previous Score".

PrevName.Text = ComboName.Text 'This works

'Once ComboName completed in the "Previous score" section, complete the other text boxes to show the previous record.

        PrevDate.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 36, False) 'date format is yyyy/mm/dd

        PrevContract.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 38, False) 'format was a tickbox so query reads true/false

        PrevPlacements.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 37, False) 'format was a tickbox so query reads true/false

        PrevScore1.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 8, False)

        PrevScore2.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 9, False)

        PrevScore3.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 10, False)

        PrevScore4.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 11, False)

        PrevScore5.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 12, False)

        PrevScore6.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 13, False)

        Prevfinance.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 14, False)

        PrevStaffing.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 15, False)

        PrevManagement.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 16, False)

        PrevScore10.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 17, False)

        PrevComments.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 35, False)

        PrevScore.Text = Application.VLookup(PrevName.Text, Range("LatestData"), 29, False)

    End With

End Sub

Answer 4

Hi Bernie

Thank you for the information. Unfortunately I am still having problems - this may be because I am working with too many worksheets. I am totally new to VBA and Queries and learning as I go (from YouTube/web articles); this means my project is probably messier then it could be. Just to explain the structure a bit more:

• The userform is reading from a table called LatestData, but needs to input new data into the table called DataTable.
• The ComboName box in the userform is looking at a range called Lookup in a worksheet called Lists.
• DataTable is a named table in a worksheet called Home_Data.
• LatestData is the name of a table in a worksheet called Latest_Data (this is a query from DataTable which sorts by date and then removes duplicates to get the most recent record for the name).  The ComboName to look up is in column H of the worksheet (or the 6th column in the named table).

Another option could be just to read from (and add to) DataTable but I would need a way to look for the last dated record of the chosen ComboName and I don't know how to do this. In the DataTable ComboName would be found in column J or the 10th column of the table.

Below is an example of the userform (I've removed sensitive data). At the moment, after selecting the comboname, a messagebox says was not found in [workbookname]LatestData!H3:H49.

Answer 5

What is in LatestData!H3:H49? Should those values match PrevName.Text? Do they match exactly?  Space, punctuation, etc. all matter with VLOOKUP using False as the last parameter (exact match).