Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell increment range | Excel Programming | |||
Auto increment chart range montly | Charts and Charting in Excel | |||
how to auto increment cell location within formula | Excel Worksheet Functions | |||
Increment array range | Excel Programming | |||
Increment cells only in filtered range? | Excel Programming |