Selecting a row range after finding a value 1

[t16turbo]

I have some code to find a value in a worksheet:

Cells.Find(What:="HTD Total", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate

I need to be able, once found, to offset by 4 rows to the right and then select the next 12 rows and copy.

basically 4 cells to the right of 'HTD Total' is a row of 12 cells I need to copy.

what is the syntax for the selecting this range?

thanks in advance,

 

[AnotherHiggins]

Using what you have as a starting point:

Cells.Find(What:="HTD Total", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
Range(ActiveCell.Offset(, 4), ActiveCell.Offset(12, 4)).Copy

From there, there are a ton of ways to combine the steps. Here is an example that does everything in one step:

Range(Cells.Find(What:="HTD Total", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Offset(, 4), Cells.Find(What:="HTD Total", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Offset(12, 4)).Copy

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

Personally, I like using it like this:

dim fCell as range

set fCell = Cells.Find(What:="HTD Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart)

If Not fCell is nothing then
  'Text found
  fCell.offset(4,12).copy
else
  'Text not found
End If

However, do you mean "rows" or "columns" as

to offset by 4 rows to the right and then select the next 12 rows and copy.

does not make any sense as rows are vertical and therefore cannot be "to the right"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[t16turbo]

thanks again - you're my hero

is it possible to specify a certain column in the FIND
e.g. look in column 'L' for "HTD Total"?

 

[AnotherHiggins]

Actually, when doing a find make sure to take a look at xlbo's suggestion, as mine would error out if the text wasn't found.

As for restricting the search to a single column, just change the Cells.Find to whatever range you want to search, Columns("L:L").Find

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[t16turbo]

this is what the code looks like (the value WILL always be in the spreadsheet - luckily)

Range(Columns("L:L").Find(What:="Adjustments", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Offset(, 5), Columns("L:L").Find(What:="Adjustments", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Offset(0, 16)).Copy

but it displays the error, "unable to get the find property of the range class"

I didn't spot Geoffs response until now!!
thanks Geoff

 

[xlbo]

....well you do have 2 find statements in there - you can't just link them together like that

What is the intended purpose of:

").Offset(, 5), Columns("L:L").Find" ????

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Geoff, t16turbo is trying to use FINDs in both arguments of the RANGE.

t16turbo : The problem is [blue]After:=ActiveCell[/blue]. If you don't have a cell in column L active before this, the code will error out. It looks like you have left everything as defaults, so just get rid of all of the code you don't need. You are left with:

Range(Columns("L:L").Find(What:="Adjustments").Offset(, 5), _
    Columns("L:L").Find(What:="Adjustments").Offset(0, 16)).Copy

In fact, you don't have to include the WHAT in there. This will work:

Range(Columns("L:L").Find("Adjustments").Offset(, 5), _
    Columns("L:L").Find("Adjustments").Offset(0, 16)).Copy

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.