Increment a range location
 

Dim Writeme as Range


Set WriteMe = Worksheets("Imp").Range(ActiveCell.Address)
Writeme.Activate
MsgBox (WriteMe.Address)
Set WriteMe = WriteMe.Range(ActiveCell.Offset(1, 0).Address)
WriteMe.Select
MsgBox (WriteMe.Address)

The first message box reports $D$1

The second message box reports $G$1)

What I want is $D$2.

Which I can get with

Set WriteMe = WriteMe.Range(ActiveCell.Offset(1, -3).Address)
But that isn't very satisfactory

What am I doing wrong here?
How can I increment a range adress?
How can I increment a range adress on a worksheet that is not active?

Increment a range location
 

You dont have an activecell unless the sheet is active...


Dim Writeme As Range
Set Writeme = Worksheets("Imp").Range("G1")
Writeme.Activate
MsgBox (Writeme.Address)
Set Writeme = Writeme.Offset(1, 0)
Writeme.Select
MsgBox (Writeme.Address)

--
If this post helps click Yes
---------------
Jacob Skaria


"Hydra" wrote:

Dim Writeme as Range


Set WriteMe = Worksheets("Imp").Range(ActiveCell.Address)
Writeme.Activate
MsgBox (WriteMe.Address)
Set WriteMe = WriteMe.Range(ActiveCell.Offset(1, 0).Address)
WriteMe.Select
MsgBox (WriteMe.Address)

The first message box reports $D$1

The second message box reports $G$1)

What I want is $D$2.

Which I can get with

Set WriteMe = WriteMe.Range(ActiveCell.Offset(1, -3).Address)
But that isn't very satisfactory

What am I doing wrong here?
How can I increment a range adress?
How can I increment a range adress on a worksheet that is not active?

Increment a range location
 

Dim Writeme As Range

Set Writeme = Worksheets("Imp").Range(ActiveCell.Address)
Writeme.Activate
MsgBox (Writeme.Address)
Set Writeme = Writeme.Offset(1, 0)
Writeme.Select
MsgBox (Writeme.Address)

this works for me
:)
susan



On Aug 11, 12:56*pm, Hydra wrote:
* * * * *Dim Writeme as Range * * * *

* * * * Set WriteMe = Worksheets("Imp").Range(ActiveCell.Address) * * *
* * * * Writeme.Activate
* * * * MsgBox (WriteMe.Address)
* * * * Set WriteMe = WriteMe.Range(ActiveCell.Offset(1, 0).Address)
* * * * WriteMe.Select
* * * * MsgBox (WriteMe.Address)

The first message box reports $D$1

The second message box reports $G$1)

What I want is $D$2.

Which I can get with

Set WriteMe = WriteMe.Range(ActiveCell.Offset(1, -3).Address)
But that isn't very satisfactory

What am I doing wrong here?
How can I increment a range adress?
How can I increment a range adress on a worksheet that is not active?

Increment a range location
 

The first message box reports $D$1

The second message box reports $G$1)

Activecell always refer to the active sheet active cell....and thats the
reason why it goes wrong.

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

You dont have an activecell unless the sheet is active...


Dim Writeme As Range
Set Writeme = Worksheets("Imp").Range("G1")
Writeme.Activate
MsgBox (Writeme.Address)
Set Writeme = Writeme.Offset(1, 0)
Writeme.Select
MsgBox (Writeme.Address)

--
If this post helps click Yes
---------------
Jacob Skaria


"Hydra" wrote:

Dim Writeme as Range


Set WriteMe = Worksheets("Imp").Range(ActiveCell.Address)
Writeme.Activate
MsgBox (WriteMe.Address)
Set WriteMe = WriteMe.Range(ActiveCell.Offset(1, 0).Address)
WriteMe.Select
MsgBox (WriteMe.Address)

The first message box reports $D$1

The second message box reports $G$1)

What I want is $D$2.

Which I can get with

Set WriteMe = WriteMe.Range(ActiveCell.Offset(1, -3).Address)
But that isn't very satisfactory

What am I doing wrong here?
How can I increment a range adress?
How can I increment a range adress on a worksheet that is not active?

Increment a range location
 

Sub WhereAmI()
Dim Writeme As Range
Set Writeme = ActiveCell
MsgBox (Writeme.Address)
Set Writeme = Writeme.Offset(1, 0)
MsgBox (Writeme.Address)
End Sub
--
Jim Cone
Portland, Oregon USA





"Hydra"
wrote in message
...
Dim Writeme as Range


Set WriteMe = Worksheets("Imp").Range(ActiveCell.Address)
Writeme.Activate
MsgBox (WriteMe.Address)
Set WriteMe = WriteMe.Range(ActiveCell.Offset(1, 0).Address)
WriteMe.Select
MsgBox (WriteMe.Address)

The first message box reports $D$1

The second message box reports $G$1)

What I want is $D$2.

Which I can get with

Set WriteMe = WriteMe.Range(ActiveCell.Offset(1, -3).Address)
But that isn't very satisfactory

What am I doing wrong here?
How can I increment a range adress?
How can I increment a range adress on a worksheet that is not active?

Increment a range location
 

to add to Jim's code, and to answer your third question, assume you
have a second spreadsheet named "Ump"...........

Option Explicit

Sub WhereAmI()

Dim Writeme As Range
Dim Whereme As Worksheet

Set Whereme = ActiveWorkbook.Worksheets("Imp")
Set Writeme = Whereme.Range("D1")
Writeme.Value = "Hello world!"
Set Writeme = Writeme.Offset(1, 0)
Writeme.Value = "Hello universe!"

Set Whereme = ActiveWorkbook.Worksheets("Ump")
Set Writeme = Whereme.Range("D2")
Writeme.Value = "Where am I?"
Set Writeme = Writeme.Offset(2, 0)
Writeme.Value = "Over here!"

End Sub

hope that helps you see how you can "move" around without activating &
selecting.
:)
susan



On Aug 11, 1:15*pm, "Jim Cone" wrote:
Sub WhereAmI()
* Dim Writeme As Range
* Set Writeme = ActiveCell
* MsgBox (Writeme.Address)
* Set Writeme = Writeme.Offset(1, 0)
* MsgBox (Writeme.Address)
End Sub
--
Jim Cone
Portland, Oregon *USA

"Hydra"
wrote in ....
(snipped)
What am I doing wrong here?
How can I increment a range adress?
How can I increment a range adress on a worksheet that is not active?