Find row number and pass it to a variable

[simora]

I have a userform with a comboBox.
On the worksheet the data Range for the comboBox is ( B5:B15)

How do I match the value selected in the combobox on the UserForm to the row number in Range ( B5:B15) and pass the row number to a variable nNum.

How do you use Index/Match in VBA code?

Thanks

[lucas]

You don't say what you want to do with the row info but this method which also populates the combobox for you will return values from the selected row using find......see attached.



Private Sub UserForm_Initialize()
With Sheets("Sheet1")
ComboBox1.List = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Value
End With
End Sub

Private Sub ComboBox1_Change()
Dim rng As Range
With Sheets("Sheet1")
Set rng = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With
With rng.Find(ComboBox1, lookat:=xlWhole)
Label1.Caption = .Offset(, 1)
Label2.Caption = .Offset(, 2)
End With
End Sub

[simora]

Thanks Lucas:

Will check it out in a couple of hours when I return.

[simora]

Lucas:

I did a quick look at the code.
I need a rownumber to be the result of this bit:


Set rng = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With
With rng.Find(ComboBox1, lookat:=xlWhole)

rng. value or something along those lines to know which row it found the match

Thanks

Edit Lucas: VBA tags added to code

[lucas]

Like this:

Private Sub ComboBox1_Change()
Dim rng As Range
With Sheets("Sheet1")
Set rng = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With
With rng.Find(ComboBox1, lookat:=xlWhole)
Label1.Caption = .Row
MsgBox .Row
End With
End Sub

or


Label1.Caption = "The item was found on row " & .Row

[simora]

That works great. Thanks

I can set rsRow= .Row
and use it as a variable.

Where is the details on how to attach a sheet &
How do you set this post as solved ?

[lucas]

To attach your workbook click on post reply at the bottom left of the last post, post your message and then scroll down and look for a button that says "manage attachments"

Mark your thread solved using the thread tools at the top of the page.

[lucas]

ps when posting code, select the code and hit the vba button to format it for the forum.