NEWBIE: How can I do a "soft reference" in Excel/VBA?
 

Hey all,

I'm trying to turn a row of data into multiple rows, as
such:
a b c 1 2 3 --- "a b c 1" and "a b c 2" and "a b c 3."

I need this to work such that if I click on row 1, it will
insert 3 rows, then do the appropriate fill, cut, and
paste. My first attempt with a macro didn't work - it was
a "hard" or "absolute" reference, so it would always do
the operations on the same row.

I tried modifying the code using "Selection.", but that
didn't work either, as I guess the selection changes each
time.

How can I move/operate relative to the cell that is
selected when the macro is run? I have found something
called Selection.Address, but I can't figure out how to
move around after that (i.e. softcode: select
Selection.Address, Selection.Address+2; fill down 5;
select Selection.Address+3; cut, paste Selection.Address
(+1, +3).

Thanks!
Dave

NEWBIE: How can I do a "soft reference" in Excel/VBA?
 

For starters, you can use variables to achieve this sort of task like in
this case, setup a range object.

Dim CurRng as Range
Set CurRng = Selection

By doing this, even if the selection changes, the variable won't.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Dave" wrote in message
...
Hey all,

I'm trying to turn a row of data into multiple rows, as
such:
a b c 1 2 3 --- "a b c 1" and "a b c 2" and "a b c 3."

I need this to work such that if I click on row 1, it will
insert 3 rows, then do the appropriate fill, cut, and
paste. My first attempt with a macro didn't work - it was
a "hard" or "absolute" reference, so it would always do
the operations on the same row.

I tried modifying the code using "Selection.", but that
didn't work either, as I guess the selection changes each
time.

How can I move/operate relative to the cell that is
selected when the macro is run? I have found something
called Selection.Address, but I can't figure out how to
move around after that (i.e. softcode: select
Selection.Address, Selection.Address+2; fill down 5;
select Selection.Address+3; cut, paste Selection.Address
(+1, +3).

Thanks!
Dave