Find text in column

[Francis Hookham]

Column E typically contains the following text entries.

As part of a module I need to find the row of the cell in which a specified
string appears.

iRowNum is the row sought.

sItem is the string to search for.

NB. There are several blanks cells between entries in the column.



iRowNum = ...sItem...?



D01-001

D01-002

D01-003

D01-004

..

..

..

D02-003

D02-004

D02-005

....



Many thanks,



Francis Hookham

 

[JLGWhiz]

This is one way:

Sub fndiRowNum()
sItem = 4
With ActiveSheet.UsedRange
Set C = .Find(sItem, LookIn:=xlValues)
If Not C Is Nothing Then
x = C.Address
iRowNum = Range(x).Row
End If
End With
MsgBox iRowNum
End Sub

 

[JLGWhiz]

Of course your sItem would be a different value but I used 4 just to run a
test.

 

[Francis Hookham]

Great! Thanks - not sure I undestand how it works but it does - I must study
it and get to grips with it.