Using Offset on Cell Address
 

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

Using Offset on Cell Address
 

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

--
Regards,
Tom Ogilvy


"kfh105" wrote in message
om...
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

Using Offset on Cell Address
 

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" wrote in message
...
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

--
Regards,
Tom Ogilvy


"kfh105" wrote in message
om...
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

Using Offset on Cell Address
 

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)

--
Regards,
Tom Ogilvy

"k" wrote in message
...

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" wrote in message
...
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

--
Regards,
Tom Ogilvy


"kfh105" wrote in message
om...
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