How to loop a formula in different cells in excel vba?

Question

I have a piece of code where I sum the cell "K6" from every sheet of the workbook appart the main one called "Data". However it is hard coded and I would like to be able to loop it. Either this or establishing a formula and than extending it. I think it will be easier to see what I'm talking about by looking at the code

This is what I have already

`Sub SumSheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "Data" Then

SumTotal = SumTotal + ws.Range("K6").Value
SumTotal2 = SumTotal + ws.Range("K7").Value
SumTotal3 = SumTotal + ws.Range("K8").Value
SumTotal4 = SumTotal + ws.Range("K9").Value

End If

Next

Sheets("Data").Range("A6").FormulaR1C1 = SumTotal
Sheets("Data").Range("A7").FormulaR1C1 = SumTotal2
Sheets("Data").Range("A8").FormulaR1C1 = SumTotal3
Sheets("Data").Range("A9").FormulaR1C1 = SumTotal4

End Sub'

This piece of code works like I want it to, but it would be much easier if I could find a way to loop it. Or maybe establish SumTotal as a function and extend it like in regular excel.

Sub SumSheets()

Dim ws As Worksheet
Dim i As Integer
Dim j As Integer

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "Data" Then

SumTotal = SumTotal + ws.Range("K6").Value
SumTotal2 = SumTotal + ws.Range("K7").Value
SumTotal3 = SumTotal + ws.Range("K8").Value
SumTotal4 = SumTotal + ws.Range("K9").Value

For i = SumTotal To SumTotal4
For j= 6 To 10

Cells(j,1).Value = i

Next j
Next i

End If

Next

End Sub'

The result with the For Loop I tried is that it only put the value of SumTotal4 in cells 6 through ten. I'm guessing it's because i haven't defined i well enough.

Tim Williams · Accepted Answer · 2019-06-11 22:39:03Z

3

As a UDF:

Function SumAll(addr As String)
    Application.Volatile
    Dim ws As Worksheet, tot
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Data" Then tot = tot + ws.Range(addr).Value
    Next ws
    SumAll = tot
End Function

Then (eg) in A6 you can enter:

=SumAll("K6")

or better:

Function SumAll(c As Range)
    Application.Volatile
    Dim ws As Worksheet, tot
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Data" Then tot = tot + ws.Range(c.Address(False, False)).Value
    Next ws
    SumAll = tot
End Function

then you can us (eg)

=SumAll(K6)

and it will adjust as you drag down

answered Jun 11, 2019 at 22:39

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SadMrFrown · Accepted Answer · 2019-06-13 21:36:17Z

0

You could also have it as a sub which concatenates a string and you can use the .Formula property to assign it to a range. Excel will automatically update the formula.

For example if you wanted A1 in "Test" to display the sum of K6 in all those worksheets whose name is not data, A2 to display all those with K7 etc.The following would work:

Sub test()
Dim str1 As String, str2 As String
Dim ws As Worksheet, i As Integer, j As Integer


j = 0

'As data pertains to one spreadsheets name

i = ThisWorkbook.Worksheets.Count - 1

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Data" Then
    j = j + 1
    If i - j = 0 Then
        str1 = "'" & ws.Name & "'!K6 "
    Else
        str1 = "'" & ws.Name & "'!K6, "
    End If
    str2 = str2 & str1
End If
Next ws

ThisWorkbook.Worksheets("Test").Range("A1:A7").Formula = "=sum(" & str2 & ")"


End Sub

The added bonus being that you can track your formula through your worksheet if that is of interest.

For your query below, modify the if function with str1 to:

    If i - j = 0 Then
        str1 = "abs('" & ws.Name & "'!E6)/('" & ws.Name & "'!K6) "
    Else
        str1 = "abs('" & ws.Name & "'!E6)/('" & ws.Name & "'!K6), "
    End If

The last line should also change:

ThisWorkbook.Worksheets("Data").Range("A1:A7").Formula = "=average(" & str2 & ")"

edited Jun 13, 2019 at 21:36

answered Jun 11, 2019 at 22:43

SadMrFrown

1573 silver badges15 bronze badges

4 Comments

Jules-Henri Revault Over a year ago

Works perfectly, thanks! I have the same question but with the following formula: Abs(E7)/K7. I want to calculate this formula on each sheet, and then have the average on the main sheet(Data) say in cell A1 for example

SadMrFrown Over a year ago

@Jules-HenriRevault I have added a few lines in answer above, let me know if that answers your question.

Jules-Henri Revault Over a year ago

It’s exactly what I was looking for. Thank you very much for your help!

SadMrFrown Over a year ago

No problem. Glad I could help.

Collectives™ on Stack Overflow

How to loop a formula in different cells in excel vba?

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related