Use Contents of ComboBox in Vlookup and Save to Data Table

[DaveGib]

Hi Everyone,
I have a problem that I have been scratching my head all day on and am asking someone to please help!
I have a user form that has 3 comboboxes on it with lists that the user can select data, then the selections are saved to a datatable. This all works fine, but my problem is that I also need to save some additional information that is dependant on the result of two of the combobox selections, and cant get the lookup to work.

Basically if "Foreman" is selected from ComboBox8, then I need to look up a rate using the Input in ComboBox6 from a Range called "MilestoneRate" from another sheet. I then need to assign the result to a variable called "Rate" and save it with the rest of the form data to a table.

I have attached the code I have used for the lookup, - I also attach the code I have used here.

Any help will be greatly appreciated............
Thanks in advance!

[KevCarter]

Dave,

I'm pretty new to VBA, so forgive me if this doesn't work. Just based on my VBA vLookup code that is working, try changing:

ComboBox6.Text

to:

Me.ComboBox6.Value

Again, apologies if this is a bonehead post, just trying to help.

Kevin

[DaveGib]

Hi Kevin,
Many thanks for the response!
I tried what you suggested, but I still get a 0 saved in column 13.
Thanks for the suggestion..........
regards
Dave

[DaveGib]

Hi,
I feel a bit sheepish, - there is nothing wrong with the code I had, ... the problem was that I had a Dim statement elsewhere, that I had not commented out when I was trying to get it to work.

If it is of any help to anyone else, here is the code

' Check if input is FOREMAN in Sign Off and get Rate for the Milestone signed off
    Dim Rate As Double

       If ComboBox8.Text = "FOREMAN" Then
         Rate = Application.WorksheetFunction.VLookup(Me.ComboBox6.Value, Worksheets("Lists").Range("J3:K22"), 2, False)
        Else
          Rate = 0
        End If

 '   And to Save to a Table :- 

 '   Determine the next empty row
    NextRow = _
      Application.WorksheetFunction.CountA(Range("A:A")) + 1
 '   Transfer the data

        Cells(NextRow, 1) = 0               ' Material Description
        Cells(NextRow, 2) = 0               ' Material Quantity Issued
        Cells(NextRow, 3) = 0               ' Material Quantity Received
        Cells(NextRow, 4) = 0               ' Task Description
        Cells(NextRow, 5) = ComboBox6.Text  ' Milestone
        Cells(NextRow, 6) = 0               ' House ID
        Cells(NextRow, 7) = ComboBox2.Text  ' House Number
        Cells(NextRow, 8) = ComboBox9.Text  ' Subbie Name
        Cells(NextRow, 9) = TextBox1.Text   ' Date of sign off
        Cells(NextRow, 10) = Todaysdate     'date captured
        Selection.NumberFormat = "dd/mm/yyyy"
        Cells(NextRow, 11) = ComboBox8.Text  ' Signed Off By
        Cells(NextRow, 12) = "Sign Offs"    ' Status Identifier
        Cells(NextRow, 13) = Rate           ' Rate
        Cells(NextRow, 14) = TextBox2.Text  ' DOH Number

[KevCarter]

Those are tough bugs to find. I'm glad you got it figured out!

Kevin