Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cell formula and adjust 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, while 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.) 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 have tried unsuccessfully to write this macro. I would gladly appreciate any solutions! Thanks! J |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cell formula and adjust column spacing
Holy Molly,
You may get better results by just asking a specific question.......... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adjust line spacing of text in a cell | Excel Discussion (Misc queries) | |||
How do I adjust marker spacing in Excel scatter plot? | Charts and Charting in Excel | |||
Adjust line spacing when printing address labels in Excell ? | Excel Worksheet Functions | |||
Copy formula with column spacing | Excel Programming | |||
How to adjust spacing between lines of wrapped text ? | Excel Discussion (Misc queries) |