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


Code:
--------------------

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,


--
matpj
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=481912

Your code needs to handle the case where what you are looking for is not
found. Here is some code for you to try...

Sub Test()
Dim rngFound As Range
Dim rngCopyArea As Range
Dim wks As Worksheet

Set wks = ActiveSheet
Set rngFound = wks.Cells.Find("HTD Total")

If rngFound Is Nothing Then
MsgBox "HTD Total was not found"
Else
Set rngCopyArea = wks.Range(rngFound.Offset(0, 4),
rngFound.Offset(0, 16))
rngCopyArea.Copy Sheets("Sheet2").Range("A1")
End If
Set wks = Nothing
Set rngFound = Nothing
Set rngCopyArea = Nothing
End Sub

--
HTH...

Jim Thomlinson


"matpj" wrote:


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


Code:
--------------------

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,


--
matpj
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=481912