VBA Form with Vlookup not working

ajmartin

New Member
Hi

I am building a form for data entry into Excel and I have all other form boxes working but cannot seem to get the vlookup of a response to a combobox to trigger an entry in a text box further down the form

[FONT=Segoe UI, Segoe UI Web (West European), Segoe UI, -apple-system, BlinkMacSystemFont, Roboto, Helvetica Neue, sans-serif]I am not sure how to debug it to see which bit is failing as I get no response to the vlookup when the form is filled in. Here is the VB I am using
[/FONT]


Private Sub VendorCountryBox_Change()


var1 = WorksheetFunction.VLookup(VendorSelectionBox.Value, Worksheets("Lookups").Range("VendorCountryList"), 3, False)


VendorCountryBox.Value = var1


End Sub


The vendor selection box is a combo list box and is working fine. I have put the VBA in as a private sub amongt the other change subs at the end of the VBA. Is the problem where I have placed the VBA or the lookup syntax?

Thanks
Andy
 

ajmartin

New Member
You're welcome & thanks for the feedback
I was just testing this and although it worked to start with the above now returns a run time error 1004 which seems to be some problem with the MS Office resource library, does this mean that the VBA form will be unstable if I release it?


Thanks
Andy
 

ajmartin

New Member
I was just testing this and although it worked to start with the above now returns a run time error 1004 which seems to be some problem with the MS Office resource library, does this mean that the VBA form will be unstable if I release it?


Thanks
Andy
it stops at

Private Sub VendorSelectionBox_Change()

var1 = WorksheetFunction.VLookup(VendorSelectionBox.Value, Worksheets("Lookups").Range("VendorCountryList"), 3, False)


VendorCountryBox.Value = var1


End Sub

But it worked yesterday and I have not changed anything!
 

Fluff

MrExcel MVP, Moderator
How about
Code:
Private Sub VendorSelectionBox_Change()
   Dim Var1 As Variant
   Var1 = Application.vlookup(VendorSelectionBox.Value, Worksheets("Lookups").Range("VendorCountryList"), 3, False)
   If Not IsError(Var1) Then
      VendorCountryBox.Value = Var1
   Else
      MsgBox VendorSelectionBox.Value & " Could not be found"
   End If
End Sub
 

ajmartin

New Member
How about
Code:
Private Sub VendorSelectionBox_Change()
   Dim Var1 As Variant
   Var1 = Application.vlookup(VendorSelectionBox.Value, Worksheets("Lookups").Range("VendorCountryList"), 3, False)
   If Not IsError(Var1) Then
      VendorCountryBox.Value = Var1
   Else
      MsgBox VendorSelectionBox.Value & " Could not be found"
   End If
End Sub
Ah ok so that works but I get the 'Could Not Be Found' Message every time
Something is not right, it returns the result, copies the correct data to the sheet and then i think as the form returns ready for the next entry it gives the message " Could not be found", I must have it in the wrong place

I might have taken on too much here, is there something that needs to be added to the lookup so it does not immediately look for the match when the form clears for the next entry?

Thanks
Andy
 

Fluff

MrExcel MVP, Moderator
Change it to a click event instead of a change event.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top