Using Offset on Cell Address

[kfh105]

Let me preface this by stating that I'm relatively new to Excel macro
programming.

The following piece of code enables me to use Offset to get my
starting y-range. I'm searching for a specific string of text and what
to perform some data calculations on the two corresponding rows of
data, which start 2 & 3 rows below the located text respectively. (See
use of Offset below.)

--

Set Rng = Range("B4:B500").Find(What:="misctext", _
LookAt:=xlWhole, _
LookIn:=xlValues)
Dim selRngstart As Range

If Rng Is Nothing Then
MsgBox "Data Not Found"
Exit Sub
Else
' finding the starting point for data interpolation range
startxrange = Rng.Offset(2, 0).Address
startyrange = Rng.Offset(3, 0).Address

--

I tried using Offset in the following example, but it is not allowing
me to do it.

--

With ActiveCell
' Returns cell address of last cell in row (form of $F$9)
endxrange = Cells(.Row, Columns.Count).End(xlToLeft).Address
' endyrange = endxrange.Offset(1, 0).Address ****doesn't
work****

MsgBox "ending x cell is " & endxrange
' MsgBox "ending y cell is " & endxrange.Offset(1, 0).Address

End With

MsgBox "ending x cell is " & endxrange
' MsgBox "ending y cell is " & endyrange

--

Can anyone explain why this fails, when the prior example works as
expected? I've tried doing various Google Web & Groups searches to no
avail, although I assume I'm not searching for the right keywords. If
their is a resource that could help me out, I'd be most appreciative.

Thanks in advance for any assistance.

k

 

[Tom Ogilvy]

endxrange is a string in the usage you show

endyrange = range(endxrange).Offset(1,0).Address
MsgBox "ending x cell is " & endxrange
MsgBox "ending y cell is " & endyrange

 

[k]

Thanks for the assistance, Tom.

Your code seemed to do the trick, as I can now get the four range-specifying
values. Is it a problem that endxrange is a string? The four values
(startxrange, endxrange, startyrange, endyrange) will be plugged into an
interpolation function, specifying the range of data to be examined.

Because the Offset function worked for both startxrange & startyrange, I
imagine those values were a range & not a string. So I created a
tempendxrange variable and set endxrange as follows.

tempendxrange = Cells(.Row, Columns.Count).End(xlToLeft).Address

endxrange = Range(tempendxrange).Address

endyrange = Range(endxrange).Offset(1, 0).Address

Not sure if it was necessary or not, but I figured it couldn't hurt.

Thanks again for the help.

k

 

[Tom Ogilvy]

the use of Address on the end of a range reference returns a string.

Dim x as String
x = Range("A1").Address
msgbox "the address is " & x

Dim x1 as Range
set x1 = Range("A1")
msgbox "the address is " & x1.Address & " or " & vbNewline & _
"the address is " & x1.Address(0,0, External:=True)