Counter variable in For Loop
 

Howdy,
I'm trying to get a relatively simple macro that will give me a
difference between values moving from the first and last values back to
the middle. ie A2-A200, A3-A199, A4-A198, et al.
This is the code that I've written.

Sub CycleDiff()
J = 300
Range("F2").Select
For Ct = 1 To 150
ActiveCell.FormulaR1C1 = "=(RC[-4])-(R[" & J & "]C[-4])"
ActiveCell.Offset(1, 0).Select
J = J - 1
Next Ct
End Sub

The problem is that the first cell creates the correct formula and the
offset moves it to the next cell down, but it is always subtracting
from B302 instead of moving up to B301, B300, etc. Is there something
wrong with my counter that it's always resetting to the original
variable instead of staying in the loop?
Thanks in advance,
AM

Counter variable in For Loop
 

Sub CycleDiff()
J = 300
Range("F2").Select
For Ct = 1 To 150
ActiveCell.FormulaR1C1 = "=(RC[-4])-(R[" & J & "]C[-4])"
ActiveCell.Offset(1, 0).Select
'
' substract 2
'
J = J - 2
Next Ct
End Sub

--
Regards,
Tom Ogilvy

" wrote:

Howdy,
I'm trying to get a relatively simple macro that will give me a
difference between values moving from the first and last values back to
the middle. ie A2-A200, A3-A199, A4-A198, et al.
This is the code that I've written.

Sub CycleDiff()
J = 300
Range("F2").Select
For Ct = 1 To 150
ActiveCell.FormulaR1C1 = "=(RC[-4])-(R[" & J & "]C[-4])"
ActiveCell.Offset(1, 0).Select
J = J - 1
Next Ct
End Sub

The problem is that the first cell creates the correct formula and the
offset moves it to the next cell down, but it is always subtracting
from B302 instead of moving up to B301, B300, etc. Is there something
wrong with my counter that it's always resetting to the original
variable instead of staying in the loop?
Thanks in advance,
AM

Counter variable in For Loop
 

another way is put this in B2 and copy down to 100

=INDIRECT("A"&(200-ROW(A2)+2)-A2)

Counter variable in For Loop
 

Works fine now. Thanks much.

Tom Ogilvy wrote:
Sub CycleDiff()
J = 300
Range("F2").Select
For Ct = 1 To 150
ActiveCell.FormulaR1C1 = "=(RC[-4])-(R[" & J & "]C[-4])"
ActiveCell.Offset(1, 0).Select
'
' substract 2
'
J = J - 2
Next Ct
End Sub

--
Regards,
Tom Ogilvy

" wrote:

Howdy,
I'm trying to get a relatively simple macro that will give me a
difference between values moving from the first and last values back to
the middle. ie A2-A200, A3-A199, A4-A198, et al.
This is the code that I've written.

Sub CycleDiff()
J = 300
Range("F2").Select
For Ct = 1 To 150
ActiveCell.FormulaR1C1 = "=(RC[-4])-(R[" & J & "]C[-4])"
ActiveCell.Offset(1, 0).Select
J = J - 1
Next Ct
End Sub

The problem is that the first cell creates the correct formula and the
offset moves it to the next cell down, but it is always subtracting
from B302 instead of moving up to B301, B300, etc. Is there something
wrong with my counter that it's always resetting to the original
variable instead of staying in the loop?
Thanks in advance,
AM