finding the address of a specific cell?

[botha822]

Dim temp_range As Range

Set temp_range = Worksheets("Template").Cells.Find(Value:="Sum. Table", _
SearchOrder:=xlByRows)

When i run this in a subroutine I get this error:
"Named arguement not found."

All I want to do is locate the cell with the value="Sum. Table" and figure out its address so I can assign it as the beginning of a continuous range that is 3 cells wide and 4 cells deep.

[Gary''s Student]

How about:

Worksheets("Template").Cells.Find(What:="Sum. Table", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

Set temp_range = Selection
--
Gary's Student


"botha822" wrote:

>
> Dim temp_range As Range
>
> Set temp_range = Worksheets("Template").Cells.Find(Value:="Sum. Table",
> _
> SearchOrder:=xlByRows)
>
> When i run this in a subroutine I get this error:
> "Named arguement not found."
>
> All I want to do is locate the cell with the value="Sum. Table" and
> figure out its address so I can assign it as the beginning of a
> continuous range that is 3 cells wide and 4 cells deep.
>
>
> --
> botha822
> ------------------------------------------------------------------------
> botha822's Profile: http://www.excelforum.com/member.php...o&userid=36752
> View this thread: http://www.excelforum.com/showthread...hreadid=570094
>
>

[Otto Moehrbach]

This code finds the cell and sets the range TheRng as you want. HTH
Otto
Set temp_range = Worksheets("Template").Cells.Find(What:="Sum. Table",
LookAt:=xlWhole)
Set TheRng = temp_range.Resize(4,3)

"botha822" <[email protected]> wrote in
message news:[email protected]...
>
> Dim temp_range As Range
>
> Set temp_range = Worksheets("Template").Cells.Find(Value:="Sum. Table",
> _
> SearchOrder:=xlByRows)
>
> When i run this in a subroutine I get this error:
> "Named arguement not found."
>
> All I want to do is locate the cell with the value="Sum. Table" and
> figure out its address so I can assign it as the beginning of a
> continuous range that is 3 cells wide and 4 cells deep.
>
>
> --
> botha822
> ------------------------------------------------------------------------
> botha822's Profile:
> http://www.excelforum.com/member.php...o&userid=36752
> View this thread: http://www.excelforum.com/showthread...hreadid=570094
>

[botha822]

hey guys thanks,

what is the difference between xlPart and xlWhole?

[Otto Moehrbach]

For instance, the text "Hello there" contains "Hello". If you use xlPart
then you will find that "Hello" because you are looking at "parts" of the
string. If you use xlWhole, then you will not find that "Hello" because
that "Hello" is not everything that is in the cell. HTH Otto
"botha822" <[email protected]> wrote in
message news:[email protected]...
>
> hey guys thanks,
>
> what is the difference between xlPart and xlWhole?
>
>
> --
> botha822
> ------------------------------------------------------------------------
> botha822's Profile:
> http://www.excelforum.com/member.php...o&userid=36752
> View this thread: http://www.excelforum.com/showthread...hreadid=570094
>