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.
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
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:=FalseWhere 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
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.
+how to cut the row and copy the row in another worksheet in excel using macro
select column in sheet1 and search in sheet2 the data of column in sheet1.next copy the data rowwise of search data and paste in another sheet3.Please solve this program..
Pingback: HCG For Weight Loss
Pingback: java properties ?? ?? ?? ??? « ?? ??
Pingback: calculator reverse mortgage
Pingback: air purifier air purifier
Pingback: comprar enlaces de texto
Pingback: Horoscopes
Pingback: lilash coupons
Pingback: shiplap siding
Pingback: dating affiliate
Pingback: collection agency rates
Pingback: James Harrison suspended
Pingback: rapidrecoverysolution.com
Pingback: Homemade Solar Heating
Pingback: high interval training
Pingback: collection agency newsletter
Pingback: Rollercoaster Creator
Pingback: wedding photographer calgary
Pingback: prodotti per pulizia
Pingback: buy princess cut engagement rings
Pingback: video sexe sur facebook
Pingback: Quality Backlink Service
Pingback: Mechanical Gaming Keyboards
Pingback: ???????????? pc
Pingback: ???????????? pc
Pingback: ???????????? pc
Pingback: ???????????? pc
Pingback: red deer photographer services