Maybe you should try recording a macro to do what you want and then post
back with the problems you run into. Not too many of us want to filter
through the details as you have them posted to try and guess at what you are
after. But if you attempt to record the code and take it one problem at at
time, someone will help you.
"Joshua" <(E-Mail Removed)> wrote in message
news:4fe74ad4-5228-41de-9c5f-(E-Mail Removed)...
> 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
>
Similar Threads
