How to modify the find function

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:41 PM GMT

Question

I am pulling data in from a website and have to use the 'Find' (Ctrl + F) function to locate the cell that I want to report, however the actual cell that I am reporting is 5 cells below the cell that is identified from the Find function. What I am having a problem with is the VBA code that will allow me to report the 5th cell below the cell that is identified from the Find function. Any light you can shine on this problem will be much appreciated.

Answer

Dan,

You can do this with the offset property

the below code assumes we are searching the activesheet.


Sub findFivedown() Dim rng As Range, r As Range Set rng = Cells.Find(What:="Skyscraper", _

After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then

 ' offset 5 rows down
 Set r = rng.Offset(5, 0)
 MsgBox "Range to report: " & r.Address
 ' if you want to select it uncomment the next line
 '  r.Select

Else

 MsgBox "skyscraper not found"

End If

End Sub

the code was tested and worked for me. Adjust it to fit your specific needs.


Question

I am pulling data in from a website and have to use the 'Find' (Ctrl + F) function to locate the cell that I want to report, however the actual cell that I am reporting is 5 cells below the cell that is identified from the Find function. What I am having a problem with is the VBA code that will allow me to report the 5th cell below the cell that is identified from the Find function. Any light you can shine on this problem will be much appreciated.

Answer

I think this is a job for the OFFSET function. If you can activate the cell 5 cells above the desired cell with the Find function, then OFFSET(activecell,5,0) will identify the cell 5 cells below (and zero columns to the right). Without some more information, I don't think I can do much else for you, but, I am sure that OFFSET will work; either as a worksheet function or a VBA function.

Good luck.

Question

I am pulling data in from a website and have to use the 'Find' (Ctrl + F) function to locate the cell that I want to report, however the actual cell that I am reporting is 5 cells below the cell that is identified from the Find function. What I am having a problem with is the VBA code that will allow me to report the 5th cell below the cell that is identified from the Find function. Any light you can shine on this problem will be much appreciated.

Answer

Have you tried using the OFFSET or ADDRESS formula? Take this example

Supose all your cell names are on column C. and you are looking for "keyword" =MATCH("keyword",C:C,0) you can use the folowing formula to locate it, and formula will give you the row number

the you can use ADDRESS to reference that cell: =ADDRESS(MATCH("keyword",C:C,0),3+N("3=column C"),4,1,)

and the you can use OFFSET to go down those 5 cells =OFFSET(INDIRECT(ADDRESS(MATCH("keyword",C:C,0),3+N("3=column C"),4,1,)),5,0,1,1)

or (and easier) you can just add "+5" on the row for the address formula like this: =INDIRECT(ADDRESS(MATCH("keyword",C:C,0)+5,3+N("3=column C"),4,1,))

of course my example is simplified, you still have to deal with: 1.- is the keyword a single text? or is between a string of text 2.- are they in different location without a pattern?

hope it helps


Question

I am pulling data in from a website and have to use the 'Find' (Ctrl + F) function to locate the cell that I want to report, however the actual cell that I am reporting is 5 cells below the cell that is identified from the Find function. What I am having a problem with is the VBA code that will allow me to report the 5th cell below the cell that is identified from the Find function. Any light you can shine on this problem will be much appreciated.

Answer

I wouldn't use the find function in code - I'd PROBABLY do a for next or while wend loop until I found a cell that contained the value I wanted - at which point, I'd know the row, so getting any other row based on that would be easy - difficult to be more precise from the information given, which is why I've given an answer more by way of a thought process - do let me know if I can help further and if it helps my direct email is aidan.heritage@virgin.net

Advertisement

©2021 eLuminary LLC. All rights reserved.