How to insert formulas into cells using vba loops

[Waimea]

I am really struggling with how to insert formulas in cells using VBA and loops and it would be very kind if someone can show me an example of how to insert different formulas in different columns.

I would like to learn so that I can use this in my workbooks.

Row1 contains headers from A1 to G1.

Row2 to row10 contains randbetween(0,10) in the Range(A2:E10)

I would like to use some kind of loop to insert the following formulas in these cells.

Formula#1 ="SUM(A2:A10)"

Formula#2 ="AVERAGE(A2:E10)"

Formula#3 = =SUMPRODUCT($B$2:$E$2;B2:E2)

I am trying to get formula#1 in column F in the range of F2:F10 and formula#2 in column G in the range of G2:G10 and formula#3 in column H.

 

[Waimea]

I think I have made some progress.

I can't figure out why this code doesn't work??

For i = 0 To 2 Step 1        
wk.Range("L3:L6").Formula = "=LARGE($J$3:$J$10,i)"
Next i

It gives me

=LARGE($J$3:$J$10;i)

.

 

[Waimea]

Dim i As Integer

For i = 1 To 5 Step 1
    wk.Range("L2:L6").Formula = "=LARGE($J$3:$J$10," & i & ")"
    'wk.Range("L2:L6").NumberFormat = "0.00"
Next i

This gives me

=LARGE($J$3:$J$10;5)

for all 5 cells. What am I doing wrong?

 

[offthelip]

You have to write it individually into each cell because the formula changes for each cell with your index i:

Dim i As Integer

For i = 1 To 5 Step 1
    Cells(i, 12) = "=LARGE($J$3:$J$10," & i & ")"
    'wk.Range("L2:L6").NumberFormat = "0.00"
Next i
End Sub

Note I always use number addressing when writing to cells specially when writng formula because one can use indices for rows and columns. Column 12 is column L

 

[Waimea]

Hi offthelip,

thank you for your reply! Your code works great!

 

[MARK858]

Or amend the formula so that it increments then you don't need to loop....

wk.Range("L2:L6").Formula = "=LARGE($J$3:$J$10,ROWS($A$1:$A1))"

 

[Waimea]

Hi, thank you for your reply!

That's is very clever!

Is there anyway to use an array to hold the formulas and calculate the values in an array?

 

[offthelip]

like this?:

Sub test2()
Dim arr(1 To 5, 1 To 1) As Variant
For i = 1 To 5 Step 1
    arr(i, 1) = "=LARGE($J$3:$J$10," & i & ")"
    'wk.Range("L2:L6").NumberFormat = "0.00"
Next i
Range("L2:L6") = arr


End Sub

 

[Waimea]

Hi, yes but I was thinking about placing all of my formulas into an array, manipulate the array and then send the values back to the spreadsheet?

 

[Waimea]

How can I change a formula in a cell in my worksheet using an userform?

Private Sub CheckBox1_Click()

If CheckBox1 = True Then
     Sheets("Dash").Range("M279").Value = Sheets("Dash").Range("M279").Formula = "=(M277/365) * 1.1"
End If

End Sub