Using Match function and comboboxes in VBA

[neilybig]

Hi,
I am trying to locate a specific row number using vba. I need it to be able to look at the contents of a combobox and search for this value in the worksheet and then return the row number that the function returns.

Once I have this value (lRow) I can use this as a cell reference

Using the below I get Run-time error 13 - Type mismatch

Private Sub CommandButton1_Click()
Dim lRow As Integer
lRow = Application.Match(ComboBox1.Value, Sheets("Trial Tracker").Range("A:A"), 0)
Cells(lRow, 14).Value = TextBox3.Value
Cells(lRow, 15).Value = TextBox4.Value
End Sub

Many Thanks as always
Neil

 

[James006]

Hi,

Are you in a situation where you must have a Match ... ? If no, you do have to deal with the potential errors ....

Also ... you do have to make the Format of your ComboBox remains consistent with the data located in Column A of your worksheet ...

Hope this will help

 

[neilybig]

Hi,
Thanks for coming back to me. It should always match as the combobox is referencing the same list. How do I check formatting? as it is reading 1,2,3,4 etc so I think it is okay

Neil

 

[James006]

Hi Neil,

Most probably your combobox does contain Text ... (even if visually you see 1,2,3 ...)

and your column A ... with almost the same info shows 1,2,3 ... but within the sheet they are considered as Numbers ...

If that is the case ...you could test the CInt() function ...

[COLOR=#333333]lRow = Application.Match(CInt(ComboBox1.Value), Sheets("Trial Tracker").Range("A:A"), 0)[/COLOR]

Hope this will help

 

[neilybig]

perfect, thanks so much

 

[James006]

perfect, thanks so much

You are Welcome ...

Glad you could fix your problem ...