Как зациклить формулу в разных ячейках в excel vba?
У меня есть фрагмент кода, в котором я суммирую ячейку «K6» с каждого листа рабочей книги, кроме основного, который называется «Данные». Однако это жестко закодировано, и я хотел бы иметь возможность зациклить его. Либо это, либо установление формулы, а затем ее расширение. Я думаю, будет легче понять, о чем я говорю, взглянув на код
Это то, что у меня уже есть
`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'
Этот фрагмент кода работает так, как я хочу, но было бы намного проще, если бы я мог найти способ зациклить его. Или, может быть, установить SumTotal как функцию и расширить ее, как в обычном 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'
Результат с циклом For Loop, который я пробовал, заключается в том, что он помещает значение SumTotal4 только в ячейки с 6 по 10. Я предполагаю, что это потому, что я не определил я достаточно хорошо.
Ответы 2
Как 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
Затем (например) в A6 вы можете ввести:
=SumAll("K6")
или лучше:
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
тогда вы можете нам (например)
=SumAll(K6)
и он будет регулироваться по мере того, как вы перетаскиваете вниз
Вы также можете использовать его как подпрограмму, которая объединяет строку, и вы можете использовать свойство .Formula, чтобы назначить его диапазону. Excel автоматически обновит формулу.
Например, если вы хотите, чтобы A1 в «Test» отображала сумму K6 на всех тех листах, имя которых не является данными, A2 отображала все листы с K7 и т. д. Будет работать следующее:
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
Дополнительным бонусом является то, что вы можете отслеживать свою формулу через свой рабочий лист, если это представляет интерес.
Для вашего запроса ниже измените функцию if с помощью str1 на:
If i - j = 0 Then
str1 = "abs('" & ws.Name & "'!E6)/('" & ws.Name & "'!K6) "
Else
str1 = "abs('" & ws.Name & "'!E6)/('" & ws.Name & "'!K6), "
End If
Последняя строка также должна измениться:
ThisWorkbook.Worksheets("Data").Range("A1:A7").Formula = "=average(" & str2 & ")"
@Jules-HenriRevault Я добавил несколько строк в ответ выше, дайте мне знать, если это ответит на ваш вопрос.
Это именно то, что я искал. Большое спасибо за Вашу помощь!
Без проблем. Рад, что смог помочь.
Работает отлично, спасибо! У меня тот же вопрос, но со следующей формулой: Abs(E7)/K7. Я хочу рассчитать эту формулу на каждом листе, а затем иметь среднее значение на основном листе (данные), например, в ячейке A1.