Find any text/value in row

[RastaBananaa]

Hi All,

I am in need of some assistance again.

So I am trying to figure out a way to have excel look through a range of rows and stop when it finds a value. This value would be text, but any value would do.

The way I set excel which column to look at is by using:

Cells.Find(what:="OwnedByCustomer", after:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select

The above finds the particular text and then moves to the row below that text. For some sheets I might have a few rows(unknown amount of rows) in this particular column which contain no data. So I need excel to look through all rows below from the current active cell and once it finds a value, stop looking further. Once the value is found, this value then needs to be copied (the copy part is the easy part)

Unfortunately, I cannot define a location of OwnedByCustomer column as this is dynamic.


Thanks in advance for your help.

RastaBananaa

 

[Fluff]

How about

Sub FndCopy2()
   Dim Fnd As Range
      Set Fnd = ActiveSheet.UsedRange.Find("OwnedByCustomer", , , xlPart, , , False, , False)
      If Not Fnd Is Nothing Then
         Fnd.Offset(2).Resize(Rows.Count - Fnd.Row - 1).SpecialCells(xlConstants)(1).Select
      Else
         MsgBox "Nothing found"
      End If
End Sub

 

[RastaBananaa]

How about

Sub FndCopy2()
   Dim Fnd As Range
      Set Fnd = ActiveSheet.UsedRange.Find("OwnedByCustomer", , , xlPart, , , False, , False)
      If Not Fnd Is Nothing Then
         Fnd.Offset(2).Resize(Rows.Count - Fnd.Row - 1).SpecialCells(xlConstants)(1).Select
      Else
         MsgBox "Nothing found"
      End If
End Sub

Hi again Fluff,

The initial test of this code has come back with a positive result. It seems to be working. I will need to integrate this into the rest of the code to see how it behaves though and will report back.

I have also run a test to see what would happen if the entire column contains no data/value and this produces a following error:

Run-time error '1004'
No cells were found.

Debug points to:

Fnd.Offset(2).Resize(Rows.Count - Fnd.Row - 1).SpecialCells(xlConstants)(1).Select


Now it is impossible that the entire column will not contain any data so this error is not a problem. I just wanted to give some feedback on the code.

Thanks again,
RastaBananaa

 

[Fluff]

If you want to prevent that error simply do this

On Error resume next
Fnd.Offset(2).Resize(Rows.Count - Fnd.Row - 1).SpecialCells(xlConstants)(1).Select
On Error Goto 0