Using the Find Function and Multiple Offset Columns

**ashleys.nl** · 09-08-2011, 12:34 PM

Hey Everyone,

I'm kinda of new to VB and you have all been so helpful. Currently I've been spending the paste little while writing a VB Excel Code that will search a worksheet for specific text ("cat") , once it finds the location of said text it copies the next cell over [Offset(, 1)] into another worksheet (Sheet2). Anywho in my actual code once I find the row I'm looking for I have to copy several columns from that row. So, I'm just curious is there a more efficient way to do this than to use the "Find" function every time? I mean I already know the row I'm looking for, so should I do the entire line of code again just to change the offset to (, 2). and again and again?

Below is the sample code I'm working with

Sub FindCode()
    Worksheets("Sheet2").Cells(1, "B").Value = Cells.Find(What:="cat", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Offset(, 1).Value
End Sub

Thanks again everyone for your help!

**ashleys.nl** · 09-08-2011, 12:35 PM

Also, how do I look through the whole worksheet not just after the ActiveCell?

Thanks again

**StephenR** · 09-08-2011, 12:40 PM

A better way is to assign a range variable to the Find and then you can also check the value is found and avoid an error if not:

Sub FindCode()
    
Dim r As Range
    Set r = Cells.Find(What:="cat", LookIn:=xlFormulas, LookAt:=xlWhole, _
                       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                       SearchFormat:=False)
    If Not r Is Nothing Then
        Worksheets("Sheet2").Cells(1, "B").Value = rfind.Offset(, 1)
        Worksheets("Sheet2").Cells(1, "C").Value = rfind.Offset(, 2)
        'etc
    End If
    
End Sub

**ashleys.nl** · 09-08-2011, 12:52 PM

Hey Stephen,

That worked great, except I had to change "rfind.Offset...." to "r.Offset". "rfind" wasn't an object. But other than that it works great! thank you soo much for all your help lately. Below is the code I'm using now for others to see.

Sub FindCode()
  
Dim r As Range
    
    Set r = Worksheets("Sheet1").Cells.Find(What:="cat", LookIn:=xlFormulas, LookAt:=xlWhole, _
                       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                       SearchFormat:=False)
    If Not r Is Nothing Then
        Worksheets("Sheet2").Cells(1, "B").Value = r.Offset(, 1).Value
        Worksheets("Sheet2").Cells(1, "C").Value = r.Offset(, 2).Value
        'etc
    End If
    
End Sub

**ashleys.nl** · 09-08-2011, 01:14 PM

Hey, Just when I think I have something solved I have more work to do....

So when I try to implement this into my master work book, I realize that my search term "Cat" appears 3 times and at each occurance there is useful information in that row that I want to take away. Is there a way to implement the code so that I could have multiple "r" values of "r1", "r2", and "r3"? Each value of course refering to their respective order of occurence.

Thanks again in advance

**ashleys.nl** · 09-08-2011, 01:27 PM

Originally Posted by ashleys.nl

Hey, Just when I think I have something solved I have more work to do....

So when I try to implement this into my master work book, I realize that my search term "Cat" appears 3 times and at each occurance there is useful information in that row that I want to take away. Is there a way to implement the code so that I could have multiple "r" values of "r1", "r2", and "r3"? Each value of course refering to their respective order of occurence.

Thanks again in advance

Currently I'm using this code , but with no luck. It finds the first occurence of "cat" twice rather than moving on to the next.

any thoughts???

Sub FindCode()
  
Dim r As Range
Dim r2 As Range
    
    Set r = Worksheets("Sheet1").Cells.Find(What:="cat", LookIn:=xlFormulas, LookAt:=xlWhole, _
                       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                       SearchFormat:=False)
    Set r2 = Worksheets("Sheet1").Cells.FindNext
    If Not r Is Nothing Then
        Worksheets("Sheet2").Cells(1, "B").Value = r.Offset(, 1).Value
        Worksheets("Sheet2").Cells(1, "C").Value = r.Offset(, 2).Value
        Worksheets("Sheet2").Cells(2, "B").Value = r2.Offset(, 1).Value
        Worksheets("Sheet2").Cells(2, "C").Value = r2.Offset(, 2).Value
    End If
    
End Sub

**StephenR** · 09-08-2011, 02:01 PM

Try this:

Sub FindCode()
  
Dim r As Range, s As String, n As Long
    
Set r = Worksheets("Sheet1").Cells.Find(What:="cat", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

If Not r Is Nothing Then
    s = r.Address
    Do
        n = n + 1
        Worksheets("Sheet2").Cells(n, "B").Value = r.Offset(, 1).Value
        Worksheets("Sheet2").Cells(n, "C").Value = r.Offset(, 2).Value
        Set r = Worksheets("Sheet1").Cells.FindNext(r)
    Loop While r.Address <> s
End If
    
End Sub