How To Find Row Position of a Particular Text
Sometimes in Excel, we need to find the row position of a particular text, maybe to be able to paste certain of data right below the corresponding text or else.
Very useful if we have an Excel Worksheet Template that we use a lot, with fix header on it.
' Input param: Text we want to look for
' Optional input params:
' Search direction (forward, backward),
' Search order (in row or column)
' Output: row position of the text being searched
Private Function pFindRowPos(sText As Variant, _
Optional SearchDirection As XlSearchDirection = xlNext, _
Optional SearchOrder As XlSearchOrder = xlByRows) As Long
Dim lResult As Long, oRg As Range
Set oRg = Cells.Find(What:=sText, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=SearchOrder, _
SearchDirection:=SearchDirection, _
MatchCase:=False, SearchFormat:=False)
If Not oRg Is Nothing Then lResult = oRg.Row
pFindRowPos = lResult
Set oRg = Nothing
End Function
FIN.
Related Entries
External Resources
- Microsoft Excel 2003/2007 Video Tutorials
Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.
- 101 Secrets of Microsoft Excel
Discover 101 of Excels little-known secrets that have been hiding right under your nose.
Tagged with: excel vba macro, find row position
andy
Can you tell me how to find a value in the first cell then move across to a specifeied row and paste a new value?
I can do the vlookup or activecell offset but cant get the rest to work
Poer
Hi Andy, we can use Excel build in OFFSET function to do that.
for example, to move 5 row below current active cell, and paste the value copied before in there, we use something like this
ActiveCell.Offset(5).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=FalseAndy
Where exactly do I put this function in the Excel code to make it work. As well, how can you make it search another worksheet versus the current one?
Andy
Excel VBA Macro
halo Andy, the function above should be called by others excel vba macro procedure or function in Excel VBA editor.
we can’t called it directly from the excel formula bar.
to make it search another worksheet is simple, just activate the worksheet we want to search before calling the function above, and the function above will search that worksheet.
in short, the function act as supporting function for other vba procedure, because the nature of the function is only to seek for text position in active worksheet.