Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula with column spacing
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula with column spacing
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count spacing violations between values in column | Excel Worksheet Functions | |||
SUBSTITUTE formula -- variable spacing between parameters?? | Excel Worksheet Functions | |||
spacing numbers in 1st column by inch intervals of 2nd column | Excel Worksheet Functions | |||
How do I change data spacing in a column | Excel Worksheet Functions | |||
double spacing a column | Excel Discussion (Misc queries) |