How can I find the index of a row for a value in excel É

is there a funct开发者_JAVA技巧ion in excel which can find the index of the row for a particular value É

ie if I write function(5.77) it will return 5 , because 5.77 appears in row 5 .

'1 Frequency '2 4.14 '3 4.19 '4 5.17 '5 5.77 '6 6.39

You can use the MATCH() worksheet function. In order to use this in VBA, you need to use the WorksheetFunction object, which exposes a subset of the Excel worksheet functions. E.g.

Dim rowIndex as Long
rowIndex = WorksheetFunction.Match(5.77, Range(A1:A6))

The function is called MATCH. It doesn't return the row number, but it returns the index of the cell inside the range, you can add the base row number to that to get the actual row number.

PS. This is an excel function used in cell formulas, not an excel-vba function...

Range.Find & Lookup are other good option to get the Row number of search string.