Finding Row Index based on Vlookup

[huntlake]

I have a spreadsheet that was designed to increment the value of a cell by 1 each time a button is clicked.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I’m pretty handy with Excel, but when it comes to VB, I’m a novice.
<o> </o>
Essentially, my columns are Item, Value (this is the cell that needs to be updated), List Number, and Row Number.
<o> </o>
The old code looks like this:
<o> </o>
Private Sub CommandButton13_Click()
a = Sheet1.Cells(198, 2)
<o> </o>
b = a + 1
<o> </o>
Sheet1.Cells(198, 2) = b
End Sub
<o> </o>
The user wants to be able to sort the data on the sheet, and of course, once sorted, the updates don’t cascade to the code, so my idea was to create a secondary sheet that would hold the data (and always stay in a particular order), and then have the first sheet point to that one. That way, when the sort order is changed, it won’t affect where the code points. Easy stuff, right?
<o> </o>
The user also wants to be able to have some combo boxes drive where the value is stored. Also, they occasionally need to be able to add new items. I want to make this process simple so that the user doesn’t have to change the code when they add a new item. So, I named the input range for the combo box. That way, when they need to add new items, all they have to do is change the reference of the named range. I created a combo box, and am having VB lookup the value of the item selected in the named range and choose the value of the row to be updated.
<o> </o>
The new code looks like this:
<o> </o>
Private Sub CommandButton14_Click()
LookupVal = Sheet2.Range("F11") ‘This is where the value is stored from the Combo box
RowNum = WorksheetFunction.VLookup(LookupVal, Worksheets("Button Sheet").Range("name_of_range").Offset(0, 3), 2, False) ‘I used an offset here because I wanted to be able to have the Combo Box display the Item names vs. the List Number that I wanted to use in the Vlookup
<o> </o>
a = Sheet2.Cells(RowNum, 2)
<o> </o>
b = a + 1
<o> </o>
Sheet2.Cells(RowNum, 2) = b
<o> </o>
End Sub
<o> </o>
The code is working beautifully, but right now, the user must maintain the field “Row Number” in the sheet in order for the code to be able to populate the RowNum variable. Is there a way to return the row index based on the vlookup instead? Or, if anyone has any other suggestions on a better way to accomplish any of these tasks – I’m open!
<o> </o>
Thanks!

 

[Weaver]

if you need to find the row number of a particular piece of data, you could use the .find command in basic, which returns a range (a cell in this instamce). It should function like a vlookup, in that it'll land on the first instance it finds.

Example:

rowNum = Worksheets("Button Sheet").columns(3).find(what:=Sheet2.Range("F11").value).row

assuming the vlookup was searching on column C.

On an unrelated note

a = Sheet2.Cells(RowNum, 2)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
b = a + 1
<o:p></o:p>
Sheet2.Cells(RowNum, 2) = b

can be expressed as

Sheet2.Cells(RowNum, 2) = Sheet2.Cells(RowNum, 2) + 1

 

[huntlake]

I appreciate your time a great deal. Thank you so much - that worked! From browsing the web, I could see that the Find command seemed to be the ticket, but I couldn't figure out the syntax.