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.

 

[offthelip]

You are obviously trying to learn how to write formula using VBA and keep asking different questions , So i will give a very simple trick for learning how to do it.
write what you think you need, but LEAVE OUT the "=" sign at the start, this will write a bit of text into the cell. Then go to the cell and manually put an equals sign in front of it. Excel will then show you where the error is. Correct the error in your code and try again

 

[Waimea]

Hi, thank you for the good advice! I tried it and it is really clever!

 

[Waimea]

I am still working on this,

how would I loop

"=SUMPRODUCT($B$2:$E$2;B2:E2)"

in batches of 40?

So that the first 40 rows shows SUMPRODUCT(B2:E2:$B$2:$E$2), the next 40 rows shows SUMPRODUCT(B42:E42:$B$42:$E$42), the next 40 rows shows SUMPRODUCT(B82:E82:$B$82:$8E$2).

 

[offthelip]

This will do it, there are various ways of change the variable DC, I thought about dividing by 40 and doing a round donw , but for three values it is jsut as easy to us if statements

Sub test()
dc = ""
For i = 1 To 120
 
 Cells(i, 1) = "=SUMPRODUCT(B" & dc & "2:E" & dc & "2:$B$" & dc & "2:$E$" & dc & "2)"
 If i = 40 Then
  dc = "4"
 End If
 If i = 80 Then
  dc = "8"
 End If
Next i


End Sub

 

[Waimea]

Hi again,

I was not clear in my earlier message, I am trying to get the formulas to increment in batches of 40.

The first batch of 40 would be

"=SUMPRODUCT($B$2:$E$2;B2:E2)"

to

"=SUMPRODUCT($B$2:$E$2;B41:E41)"

.

The next batch of 40 would be

"=SUMPRODUCT($B$43:$E$43;B42:E42)"

to

"=SUMPRODUCT($B$43:$E$43;B81:E81)"

.

Following your good example, I am trying to OFFSET the formulas by 40 from row2 to row41, from row42 to row81 all the way to row1601.

I have tried select case and offset without success!

 

[offthelip]

Remember that what you are trying to do with your VBA is write out a bit of TEXT, so forget using difficult functions and formula just write out the text jsut as you would manually, this very simple modification of my code will do it:

Sub test()
dc = ""
For i = 2 To 120
 
 Cells(i, 1) = "=SUMPRODUCT($B$" & dc & "2:$E$" & dc & "2:B" & i & ":E" & i & ")"
 If i = 40 Then
  dc = "4"
 End If
 If i = 80 Then
  dc = "8"
 End If
Next i




End Sub

 

[Waimea]

Hi offthelip,

thank you very much for this! This is exellent and you are a superstar!

 

[Waimea]

Is there someway to improve this code?

Sub testtest()


dc = ""
For i = 2 To 1601
 
 Cells(i, 1) = "=SUMPRODUCT($B$" & dc & "2:$E$" & dc & "2:B" & i & ":E" & i & ")"
 If i = 40 Then
  dc = "4"
 End If
 
 If i = 80 Then
  dc = "8"
 End If
 
 If i = 120 Then
  dc = "12"
 End If
 
 If i = 120 Then
  dc = "12"
 End If
 
 If i = 160 Then
  dc = "16"
 End If
 
 If i = 200 Then
  dc = "20"
 End If
 
 If i = 240 Then
  dc = "24"
 End If
 
If i = 280 Then
  dc = "28"
 End If
 
If i = 320 Then
  dc = "32"
 End If
 
If i = 360 Then
  dc = "36"
 End If
 
If i = 400 Then
  dc = "40"
 End If
 
If i = 440 Then
  dc = "44"
 End If
 
If i = 480 Then
  dc = "48"
 End If
 
If i = 520 Then
  dc = "520"
 End If
 
If i = 560 Then
  dc = "56"
 End If
  
Next i


End Sub

I could do this manually all the way to 1600 but that seems unnecessary?

 

[offthelip]

there is a very easy way of doing this:

dc = ""
For i = 2 To 1601


  tt = i Mod 40
   If tt = 0 Then
    dc = CStr(i / 10)
   End If
  Next i

 

[Waimea]

Thank you for your reply, your code really saved me a lot of typing.

"=SQRT(SUMSQ($B$2:$H$2))*SQRT(SUMSQ(B2:H2))"

How would I rewrite this to work with your dc varible?