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 = )

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 = )

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