Need assistance with for loop in vba

[ak368]

I am unsure how to correct this code in VBA.

Sub Macro4()
'
' Macro4 Macro
'


'
For i = 2 To 13
Dim Speed As Double
    
    Speed = Cells(i, 9).Value
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=(R2C13*0.89/Speed)*((RC[-2]*0.89/Speed)^(R2C13-1))*(EXP(-((RC[-2]*0.89/Speed)^R2C13)))"
    A = ActiveCell.Value
    Selection.AutoFill Destination:=Range("C2:C21"), Type:=xlFillDefault
    Cells(i, 10) = "=R22C4"
    B = ActiveCell.Value
    Next i
    
End Sub

The result is debugged in this image below.

http://tinypic.com/r/33usx6s/9

Here is the screenshot of what happens when I click run on the excel file.

http://tinypic.com/r/1495j7s/9

I am new to vba. I don't know why in the first image, there is an Error 2029 displayed when debugging for both the variables. Does this mean that there is something wrong with my formula? How do I correct this.

I'm trying to get the VBA code to run so that the summation in column 4 row 22 is outputted onto each row of column 10 for the monthly kW calculations. Appreciate any help.

 

[ak368]

https://ibb.co/jyx1fA
https://ibb.co/eDEgfA
Sorry, please view these.

 

[Michael M]

Maybe this.
You haven't dimmed I as Integer and "spped" is treated differntly when applied to a formula

Sub Macro4()
Dim Speed As Double, i As Integer
For i = 2 To 13
    Speed = Cells(i, 9).Value
    Range("C2:C21").FormulaR1C1 = _
        "=(R2C13*0.89/" & Speed & ")*((RC[-2]*0.89/" & Speed & ")^(R2C13-1))*(EXP(-((RC[-2]*0.89/" & Speed & ")^R2C13)))"
    A = ActiveCell.Value
    Cells(i, 10) = "=R22C4"
    B = ActiveCell.Value
    Next i
    
End Sub

 

[ak368]

Maybe this.
You haven't dimmed I as Integer and "spped" is treated differntly when applied to a formula

Sub Macro4()
Dim Speed As Double, i As Integer
For i = 2 To 13
    Speed = Cells(i, 9).Value
    Range("C2:C21").FormulaR1C1 = _
        "=(R2C13*0.89/" & Speed & ")*((RC[-2]*0.89/" & Speed & ")^(R2C13-1))*(EXP(-((RC[-2]*0.89/" & Speed & ")^R2C13)))"
    A = ActiveCell.Value
    Cells(i, 10) = "=R22C4"
    B = ActiveCell.Value
    Next i
    
End Sub

This works great! However, attached is another screenshot of the result.
I want it to update each time after each iteration in the for loop rather than giving the same value of 3.975 at i=13 for all of them. Do you have any idea of how I may do this?

The Cells(i,10)="=R22C4" might be causing this, as after i=13, the value is 3.975, and all the remaining values update to the same value as it goes through the loop.

Attached is the image link.

https://ibb.co/bFYXLA

 

[Michael M]

You're right....this line

Cells(i, 10) = "=R22C4"

will always put the =R22C4 into line i

But what do you want it to update to, ie, what cell reference ??

 

[ak368]

You're right....this line

Cells(i, 10) = "=R22C4"

will always put the =R22C4 into line i

But what do you want it to update to, ie, what cell reference ??

I want it to update every time in the loop. So for example, for i=2, the summation value should be 5.834. For i=3, 3.975, ... i=4 6.039. In other words, I want a way of somehow storing the value of the summation in R4C22 after each iteration in the for loop into column 10.

The variable that is changing is the Hub avg wind speed, or the "Speed" variable in each iteration. But I need a way to store the value of R4C22 at the end of each loop to a new cell.. namely, R2C10 to R13C10.

 

[Michael M]

IS there a SUM formula in D24 ??

 

[Michael M]

Maybe this

Sub Macro4()
Dim Speed As Double, i As Integer
For i = 2 To 13
    Speed = Cells(i, 9).Value
    Range("C2:C21").FormulaR1C1 = _
        "=(R2C13*0.89/" & Speed & ")*((RC[-2]*0.89/" & Speed & ")^(R2C13-1))*(EXP(-((RC[-2]*0.89/" & Speed & ")^R2C13)))"
    Cells(i, 10).Value = cells(22,4).value
    Next i
    
End Sub

 

[ak368]

IS there a SUM formula in D24 ??

Yes, D22 sums D2:D21.

 

[ak368]

Maybe this

Sub Macro4()
Dim Speed As Double, i As Integer
For i = 2 To 13
    Speed = Cells(i, 9).Value
    Range("C2:C21").FormulaR1C1 = _
        "=(R2C13*0.89/" & Speed & ")*((RC[-2]*0.89/" & Speed & ")^(R2C13-1))*(EXP(-((RC[-2]*0.89/" & Speed & ")^R2C13)))"
    Cells(i, 10).Value = cells(22,4).value
    Next i
    
End Sub

Yes that works! You must be some kind of guru. This will help simplify it a lot!
Before you go, can you briefly explain what modifications you made to the code so that I may understand it better?