Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy formula across while skipping columns
I'm trying to write a macro that will:
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 2)macro copies the formula from the first cell in the range and pastes it into the remaining cells in the selected range, while skipping a user defined number of columns before macro: A B C D E F G 1 10 2 3 10 20 30 40 after macro: A B C D E F G 1 10 20 30 40 2 3 10 20 30 40 the formula in A1 is =A3. I'd like to be able to select A1:G1 and run the macro so that C1 refers to B3, E1 refers to C3, etc. I'd like this to also work where the formula contains a combination of one or more links and/or arithmetic operations. Would be awesome if this could also work vice-a-versa. Thanks in advance for your help on this. I have absolutely no idea how to write this kind of thing, please help! I've made my question MUCH more concise so it should be clear now = ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy formula across while skipping columns
This allows you complete control of which row, which columns and the interval.
nSkip=2 means every other column, etc. Sub skipSome() nRow = 3 'which row nFirst = 7 'starting column nLast = 39 'ending column nSkip = 2 'interval For i = nSkip To nLast Step nSkip With Cells(nRow, nFirst) .Copy .Offset(0, i) End With Next End Sub -- Gary''s Student - gsnu200833 "Joshua" wrote: I'm trying to write a macro that will: 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 2)macro copies the formula from the first cell in the range and pastes it into the remaining cells in the selected range, while skipping a user defined number of columns before macro: A B C D E F G 1 10 2 3 10 20 30 40 after macro: A B C D E F G 1 10 20 30 40 2 3 10 20 30 40 the formula in A1 is =A3. I'd like to be able to select A1:G1 and run the macro so that C1 refers to B3, E1 refers to C3, etc. I'd like this to also work where the formula contains a combination of one or more links and/or arithmetic operations. Would be awesome if this could also work vice-a-versa. Thanks in advance for your help on this. I have absolutely no idea how to write this kind of thing, please help! I've made my question MUCH more concise so it should be clear now = ) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy formula across while skipping columns
On Feb 14, 6:19*am, Gary''s Student
wrote: This allows you complete control of which row, which columns and the interval. nSkip=2 means every other column, etc. Sub skipSome() nRow = 3 * *'which row nFirst = 7 *'starting column nLast = 39 *'ending column nSkip = 2 * 'interval * * For i = nSkip To nLast Step nSkip * * With Cells(nRow, nFirst) * * * * .Copy .Offset(0, i) * * End With Next End Sub -- Gary''s Student - gsnu200833 "Joshua" wrote: I'm trying to write a macro that will: 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 2)macro copies the formula from the first cell in the range and pastes it into the remaining cells in the selected range, while skipping a user defined number of columns before macro: * *A * * * B * * * C * * * D * * * E * * * F * * * G 1 *10 2 3 *10 * * *20 * * *30 * * *40 after macro: * *A * * * B * * * C * * * D * * * E * * * F * * * G 1 *10 * * * * * * *20 * * * * * * *30 * * * * * * *40 2 3 *10 * * *20 * * *30 * * *40 the formula in A1 is =A3. *I'd like to be able to select A1:G1 and run the macro so that C1 refers to B3, E1 refers to C3, etc. *I'd like this to also work where the formula contains a combination of one or more links and/or arithmetic operations. *Would be awesome if this could also work vice-a-versa. Thanks in advance for your help on this. *I have absolutely no idea how to write this kind of thing, please help! *I've made my question MUCH more concise so it should be clear now = ) Thank you! this is very helpful. However, I'd like to do this by selecting a range in the fashion I explained in my original question. Does anyone know how to do this? Thanks, Joshua |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum skipping columns | Excel Discussion (Misc queries) | |||
copy across formula while skipping columns | Excel Programming | |||
copy across formula while skipping columns | Excel Programming | |||
Find row and insert formula, skipping columns | Excel Programming | |||
Copy formula with skipping rows | Excel Worksheet Functions |