loop for shifting one column after each calculation

**burnmeister** · 04-02-2013, 03:40 AM

Good day

I have a dataset arranged in columns. I need to calculate the slope that exists between the data of two columns, and repeat it for subsequent columns. However, the array of y-values remain the same with each calculation, but the array of x values moves 1 column forward after each calculation.

My sub for the first two calculations looks something like this:

Sub slopes()
'
' slopes Macro
'

'
    ActiveCell.FormulaR1C1 = "=SLOPE(R[-50]C[-1]:R[-3]C[-1],R[-50]C:R[-3]C)"
    Range("C51").Select
    
    ActiveCell.FormulaR1C1 = "=SLOPE(R[-50]C[-2]:R[-3]C[-2],R[-50]C:R[-3]C)"
    Range("D51").Select
End Sub

How do I create a loop to automatically repeat this process till the last column? I have attached the excel worksheet if that helps...
Sorry if this is an elementary question - I am still new in the VBA world...

Regards
Brand

**Wormsek** · 04-02-2013, 04:00 AM

Use "for" loop:

sub aaa()
dim i as integer

for i = 1 to 10
cells(51, i).FormulaR1C1 = "=SLOPE(R[-50]C[-1]:R[-3]C[-1],R[-50]C:R[-3]C)"
next i

end sub

**burnmeister** · 04-02-2013, 06:50 AM

Thank you for the reply.

However, the program still moves the y-range column for each subsequent calculation - remember that I want only the the x-range to shift for each calculation while the y range remains in the same column...

Please can you give me some hints on how to fix this...

Regards
Brand