J
Joshua
guys-
this is a bit tricky to explain so I'm going to do it in pieces so
hopefully it makes more sense.
starting point: I have a cell that's formula contains at least 1 link
to another cell within the same worksheet, same workbook, or external
workbook
keep in mind the formula could contain:
one link - i.e. Sheet1!A4 or;
more than one link - i.e. Sheet1!A1+Sheet1!A5+Sheet1!A5 or ;
a link and another arithmetic operation - i.e. = Sheet1!A4*12 or;
more than one link and another arithmetic operation - i.e. (Sheet1!
A1+Sheet1!A5+Sheet1!A5)*12
I'm trying to write a macro that will allow you to:
1) select a range of cells within the same row - where the first cell
in the range contains a formula with a link to another cell ("starting
point")
2)copy the formula from the "starting point" and paste to the
remaining cells in the selected range, which skipping a user defined
number of columns in the formula range and reference range
for example:
formula range - the range of cells to which the formulas will be
pasted into
reference range(s) - the range of cells from which the pasted formulas
will refer refer to
scenarios:
reference range contains no column spacers and formula range has
column spacers or;
formula range has column spacers, and reference range contains no
column spacers
id like to create an input box that will take two inputs - reference
range and formula range. here is an example:
reference range: 1
formula range: 2
In this instance, the reference range has no column spacers as
represented by the user input of 1 (i.e. the data/formulas are located
in sequential columns A, B, C, etc.) and;
the formula range has column spacers as represented by the user input
of 2 (i.e. the data/formulas are located in every other column A, C,
E, G, etc. i.e. 1 column spacer between each piece of data/formula)
the macro would look at the reference range(s) and see that each of
the reference range(s) contains data/formulas in sequential columns.
it would then paste the formula from the left most column of the
formula range (which refers to the reference range) to the remaining
cells in the formula range, skipping columns in intervals of 1.
The most simple example of this would be your reference range
contained data {1,2,3,4} in {A1, B1, C1, D1} and you want to link
directly to this data while skipping regular intervals of 1 column. so
your formula range after running the macro would be {1,0,2.0,3,0,4}
in {A1, B1, C1, D1, E1, F1, G1}. However this should work for
"starting points" that contain 1 or more links and/or arithmetic
operations (as stated above)
reference range: 2
formula range: 1
In this instance, the reference range has column spacers as
represented by the user input of 2 (i.e. the data/formulas are located
in every other column A, C, E, G, etc. i.e. 1 column spacer between
each piece of data/formula)
the formula range has no column spacers as represented by the user
input of 1 (i.e. the formulas are located in sequential columns A, B,
C, etc.) and;
The most simple example of this would be your reference range
contained data {1,0,2.0,3,0,4} in {A1, B1, C1, D1, E1, F1, G1} and you
want to link directly to this data in sequential order so your formula
range after running the macro would be {1,2,3,4} in {A1, B1, C1, D1} .
However, this should work for "starting points" than contain 1 or more
links and/or arithmetic operations (as stated above
I'd like this to work for user input of up to 4 (maximum of 3 empty
cells in between each formula).
I think I am pretty far away from the solution. I'm sorry if I wrote
too much but just trying to be extra clear. I'd appreciate any smart
solutions as I'm sure you guys can figure out a much better way to do
this!
Thanks!
J
this is a bit tricky to explain so I'm going to do it in pieces so
hopefully it makes more sense.
starting point: I have a cell that's formula contains at least 1 link
to another cell within the same worksheet, same workbook, or external
workbook
keep in mind the formula could contain:
one link - i.e. Sheet1!A4 or;
more than one link - i.e. Sheet1!A1+Sheet1!A5+Sheet1!A5 or ;
a link and another arithmetic operation - i.e. = Sheet1!A4*12 or;
more than one link and another arithmetic operation - i.e. (Sheet1!
A1+Sheet1!A5+Sheet1!A5)*12
I'm trying to write a macro that will allow you to:
1) select a range of cells within the same row - where the first cell
in the range contains a formula with a link to another cell ("starting
point")
2)copy the formula from the "starting point" and paste to the
remaining cells in the selected range, which skipping a user defined
number of columns in the formula range and reference range
for example:
formula range - the range of cells to which the formulas will be
pasted into
reference range(s) - the range of cells from which the pasted formulas
will refer refer to
scenarios:
reference range contains no column spacers and formula range has
column spacers or;
formula range has column spacers, and reference range contains no
column spacers
id like to create an input box that will take two inputs - reference
range and formula range. here is an example:
reference range: 1
formula range: 2
In this instance, the reference range has no column spacers as
represented by the user input of 1 (i.e. the data/formulas are located
in sequential columns A, B, C, etc.) and;
the formula range has column spacers as represented by the user input
of 2 (i.e. the data/formulas are located in every other column A, C,
E, G, etc. i.e. 1 column spacer between each piece of data/formula)
the macro would look at the reference range(s) and see that each of
the reference range(s) contains data/formulas in sequential columns.
it would then paste the formula from the left most column of the
formula range (which refers to the reference range) to the remaining
cells in the formula range, skipping columns in intervals of 1.
The most simple example of this would be your reference range
contained data {1,2,3,4} in {A1, B1, C1, D1} and you want to link
directly to this data while skipping regular intervals of 1 column. so
your formula range after running the macro would be {1,0,2.0,3,0,4}
in {A1, B1, C1, D1, E1, F1, G1}. However this should work for
"starting points" that contain 1 or more links and/or arithmetic
operations (as stated above)
reference range: 2
formula range: 1
In this instance, the reference range has column spacers as
represented by the user input of 2 (i.e. the data/formulas are located
in every other column A, C, E, G, etc. i.e. 1 column spacer between
each piece of data/formula)
the formula range has no column spacers as represented by the user
input of 1 (i.e. the formulas are located in sequential columns A, B,
C, etc.) and;
The most simple example of this would be your reference range
contained data {1,0,2.0,3,0,4} in {A1, B1, C1, D1, E1, F1, G1} and you
want to link directly to this data in sequential order so your formula
range after running the macro would be {1,2,3,4} in {A1, B1, C1, D1} .
However, this should work for "starting points" than contain 1 or more
links and/or arithmetic operations (as stated above
I'd like this to work for user input of up to 4 (maximum of 3 empty
cells in between each formula).
I think I am pretty far away from the solution. I'm sorry if I wrote
too much but just trying to be extra clear. I'd appreciate any smart
solutions as I'm sure you guys can figure out a much better way to do
this!
Thanks!
J