Find text in column
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 |
Find text in column
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 "Francis Hookham" wrote: 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 |
Find text in column
Of course your sItem would be a different value but I used 4 just to run a
test. "Francis Hookham" wrote: 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 |
Find text in column
Great! Thanks - not sure I undestand how it works but it does - I must study
it and get to grips with it. "JLGWhiz" wrote in message ... 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 "Francis Hookham" wrote: 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 |
All times are GMT +1. The time now is 11:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com