Populate a textbox based on combobox selection and other conditions

[DanSMT]

All,

Im trying to figure out why the following code is not working... Im trying to pull data from a sheet into a textbox based on the change of a combobox. See below;

Private Sub ComboBox10_Change()

Dim i As Long
Dim lastrow As Long
Dim ws As Worksheet

Me.TextBox13 = ""

Set ws = Sheets("paste log")

lastrow = ws.Range("f" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow

If Me.ComboBox11.Value = ws.Cells(i, "b") & Me.ComboBox8.Value = ws.Cells(i, "c") Then

Me.TextBox13 = ws.Cells(i, "f").Text

End If

Next i

End Sub

If i run the following code the code runs, but only brings the latest date in relation to a "NA" line on the spreadsheet and then stops.

Private Sub ComboBox10_Change()

Dim i As Long
Dim lastrow As Long
Dim ws As Worksheet

Me.TextBox13 = ""

Set ws = Sheets("paste log")

lastrow = ws.Range("f" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow

If Me.ComboBox10.Value = ws.Cells(i, "e") Then

Me.TextBox13 = ws.Cells(i, "f").Text

End If

Next i

End Sub

Any help would be greatly appreciated. Thanks!!

 

[Fluff]

You need to replace the & with And on this line

If Me.ComboBox11.Value = ws.Cells(i, "b") & Me.ComboBox8.Value = ws.Cells(i, "c") Then

 

[DanSMT]

Thanks Fluff.

However, I decided to revert back to the single line if statement and its not working if I try to look at a different column. Not quite understanding why,

Private Sub ComboBox10_Change()

Dim i As Long
Dim lastrow As Long
Dim ws As Worksheet

'Me.TextBox13 = ""

Set ws = ThisWorkbook.Sheets("paste log")

lastrow = ws.Range("b" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow

If Me.ComboBox11.Value = ws.Cells(i, "b") Then

Me.TextBox13 = ws.Cells(i, "f").Value

End If

Next i

End Sub

Changing from combobox 10 and column e to combobo11 and column b does not show anything in the textbox.

 

[Fluff]

What sort of values do you have in the combo?

 

[DanSMT]

just noticed the "b" in the lastrow statement. fixed that, but still doesnt work.

 

[DanSMT]

numbers, but they are part of a cascading combobox setup some times they are numbers sometimes they are letters

 

[Fluff]

In that case try

If Me.ComboBox11.Value = ws.Cells(i, "b").Text Then

 

[DanSMT]

In that case try

If Me.ComboBox11.Value = ws.Cells(i, "b").Text Then

this is pulling the date in the last row. not the date from the same row. I must have made a mistake elsewhere

 

[Fluff]

Will the combo11 value appear just onve in col B, or multiple times?

 

[DanSMT]

Will the combo11 value appear just onve in col B, or multiple times?

It could appear multiple times. Currently I only have a single occurance entered. Then intent is to have multiple entries