Excel VBA Userform - VLookup error

Question

Excel VBA Userform - VLookup error

Anonymous

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

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Answer accepted by question author

5 additional answers

Answer 1

Anonymous

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)