Populate ComboBox from Worksheet run time error 1004

[ipbr21054]

Hi,
Below is the code in use.
I would like to select an item from the ComboBox & once selected it should then populate TextBoxFind.
The ComboBox should be populated from my Worksheet called POSTAGE in column B9:B

Currently when i select the drop down on the ComboBox i see a RTE 1004,when i debug this line is in yellow.
LastRow = Worksheets("POSTAGE").Range(“C” & Rows.Count).End(xlUp).Row

But i dont see the reason why,please could you also check the code for both the ComboBox supplied.

Thanks

Private Sub CloseUserForm_Click()
Unload SearchAndFindPostage
End Sub
Private Sub ComboBox1_DropButtonClick()
Dim i As Long, LastRow As Long
LastRow = Worksheets("POSTAGE").Range(“C” & Rows.Count).End(xlUp).Row
If Me.ComboBox1.ListCount = 0 Then
For i = 3 To LastRow
Me.ComboBox1.AddItem Sheets(“POSTAGE”).Cells(i, “C”).Value
Next i
End If
End Sub
Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets(“POSTAGE”).Range(“C” & Rows.Count).End(xlUp).Row
For i = 3 To LastRow
If Sheets(“POSTAGE”).Cells(i, “C”).Value = (Me.ComboBox1) Or _
Sheets(“POSTAGE”).Cells(i, “C”).Value = Val(Me.ComboBox1) Then
Me.TextBoxFind = Sheets(“POSTAGE”).Cells(i, “C”).Value
End If
Next
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub ReplaceVehicleButton_Click()
    Dim lRow As Long, rng As Range, f As Range

    With Worksheets("POSTAGE")
        lRow = .Cells(.Rows.Count, 3).End(xlUp).Row
        Set rng = .Range("C9:C" & lRow)
        Set f = rng.Find(TextBoxFind.Text, , xlValues, xlWhole)
        If f Is Nothing Then
            MsgBox TextBoxFind.Text & " Was Not Found, Please Try Again", vbCritical, "UPDATE VEHICLE INFO MESSAGE"
            TextBoxFind.SetFocus
            
        Else
            rng.Replace What:=TextBoxFind.Text, Replacement:=TextBoxReplace.Text, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
            MsgBox "WORKSHEET UPDATED SUCCESSFULLY", vbInformation, "UPDATE VEHICLE INFO MESSAGE"
        End If
        TextBoxFind.Value = ""
        TextBoxReplace.Value = ""
    End With
End Sub
Private Sub TextBoxFind_Change()
    TextBoxFind = UCase(TextBoxFind)
End Sub

 

[steve the fish]

Use " not “. You have quite a few you need to change.

 

[ipbr21054]

Sorry ?
Can you explain not ?

The form works fine Then i added the ComboBox & stuck with its operation

 

[steve the fish]

Use straight ". Do not use slanted. Highlighted in red are the wrong ones.

LastRow = Worksheets("POSTAGE").Range(“C” & Rows.Count).End(xlUp).Row

 

[ipbr21054]

Ok i see that now.
The code below has been updated & now i can select the drop down arrow without the RTE message BUT the list is empty.

Private Sub ComboBox1_DropButtonClick()
Dim i As Long, LastRow As Long
LastRow = Sheets("POSTAGE").Range("C" & Rows.Count).End(xlUp).Row
If Me.ComboBox1_DropButtonClick.ListCount = 0 Then
For i = 3 To LastRow
Me.ComboBox1_DropButtonClick.AddItem Sheets("POSTAGE").Cells(i, "C").Value
Next i
End If
End Sub
Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets("POSTAGE").Range("C" & Rows.Count).End(xlUp).Row
For i = 3 To LastRow
If Sheets("POSTAGE").Cells(i, "C").Value = (Me.ComboBox1_DropButton) Or _
Sheets("POSTAGE").Cells(i, "C").Value = Val(Me.ComboBox1_DropButton) Then
Me.TextBoxFind = Sheets("POSTAGE").Cells(i, "C").Value
End If
Next
End Sub

 

[ipbr21054]

So this is the lastest code.
ComboBox is now populated BUT its also getting info from before row 9
I changed the "C" to "C9:C" but it made no difference.

Private Sub ComboBox1_DropButtonClick()
Dim i As Long, LastRow As Long
LastRow = Sheets("POSTAGE").Range("C" & Rows.Count).End(xlUp).Row
If Me.ComboBox1.ListCount = 0 Then
For i = 3 To LastRow
Me.ComboBox1.AddItem Sheets("POSTAGE").Cells(i, "C").Value
Next i
End If
End Sub
Private Sub ComboBox1_DropButtonChange()
Dim i As Long, LastRow As Long
LastRow = Sheets("POSTAGE").Range("C" & Rows.Count).End(xlUp).Row
For i = 3 To LastRow
If Sheets("POSTAGE").Cells(i, "C").Value = (Me.ComboBox1_DropButton) Or _
Sheets("POSTAGE").Cells(i, "C").Value = Val(Me.ComboBox1_DropButton) Then
Me.TextBoxFind = Sheets("POSTAGE").Cells(i, "C").Value
End If
Next
End Sub

 

[ipbr21054]

OK
New update but unable to now proceed.
I might be asking the code to do something its not correctly written for but here we go

Worksheet is called POSTAGE
Range in question is B9 then down to last row.
My userform consists of ComboBox1 & TextBoxFind

I select the drop down arrow in the ComboBox & make my selection.
My selection is now shown in the TextBoxFind field.

The code below is written so column A poulates the ComboBox1 & then the TextBoxFind field is the value of column C
example
A3 = Singer so ComBoBox shows Singer
C3 = Tom Jones so TextBoxFind Shows Tom Jones

BUT i need both to be taken from column C only.
A3 = Tom Jones so ComBoBox shows Tom Jones
C3 = Tom Jones so TextBoxFind Shows Tom Jones

Please advise.

Private Sub ComboBox1_DropButtonClick()
Dim i As Long, LastRow As Long
LastRow = Sheets("POSTAGE").Range("C" & Rows.Count).End(xlUp).Row
If Me.ComboBox1.ListCount = 0 Then
For i = 9 To LastRow
Me.ComboBox1.AddItem Sheets("POSTAGE").Cells(i, "A").Value
Next i
End If
End Sub
Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp).Row
For i = 9 To LastRow
If Sheets("POSTAGE").Cells(i, "A").Value = (Me.ComboBox1) Or _
Sheets("POSTAGE").Cells(i, "A").Value = Val(Me.ComboBox1) Then
Me.TextBoxFind = Sheets("POSTAGE").Cells(i, "C").Value
End If
Next
End Sub

 

[ipbr21054]

Any advice please as to why this is now happening ?

Thanks

 

[steve the fish]

Im a bit confused. Your combobox is being filled from the data in A9:A & Lastrow. Why are you talking about A3 and C3?

 

[ipbr21054]

Steve the fish sorry about that.
Got very confused & had to then look at plan B

Thanks for the reply.