PDA

View Full Version : [SOLVED] Use Contents of ComboBox in Vlookup and Save to Data Table



DaveGib
01-03-2014, 11:58 AM
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
01-03-2014, 01:09 PM
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
01-03-2014, 01:25 PM
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
01-03-2014, 11:27 PM
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.:omg2:

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
01-04-2014, 04:21 AM
Those are tough bugs to find. I'm glad you got it figured out!

Kevin