Looping sheets with special function in vba
I am very new to vba and my problem is this: I wrote the SCosts function
Public Function SCosts(x As Range, y As Range) As Double
Dim n As Integer
Dim Z As Double
Dim W As Double
For n = 1 To y.Columns.Count
If (y.Cells(1, n) > 8) And (y.Cells(1, n) <> "") Then
Z = Z + 8 * x.Cells(1, n) / y.Cells(1, n)
End If
If (y.Cells(1, n) <= 8) And (y.Cells(1, n) <> "") Then
W = W + x.Cells(1, n)
End If
Next n
SCosts = Z + W
End Function
which is working.
Now I have 20 sheets where I want to evaluate SCosts-fct on two ranges of each sheet and then sum them up. I wrote the following ACosts function that traverses all sheets. But it doesn't work. Can anyone help me?
Public Function ACosts(t As Variant, u As Variant)
Dim R As Variant
Dim Z As Double
Dim Ressource(1 To 2) As Variant
Ressource(1) = "SHEET1"
Ressource(2) = "SHEET2"
....
Ressource(20)= "SHEET2"
For Each R In Ressourcen
Z = Z + SCosts(Application.Goto(ActiveWorkbook.Sheets(R).Range(t)), Application.Goto(ActiveWorkbook.Sheets(R).Range(u)))
Next R
ACosts = Z
End Function
source to share
I think you were very close to getting there. I've changed some of the array and loop methods to the ones I prefer.
Public Function ACosts(t As Variant, u As Variant)
Dim Z As Double
Dim r As Long, Ressource As Variant
Ressource = Array("SHEET1", "SHEET2")
With ActiveWorkbook
For r = LBound(Ressource) To UBound(Ressource)
Z = Z + SCosts(.Sheets(Ressource(r)).Range(t), .Sheets(Ressource(r)).Range(u))
Next r
End With
ACosts = Z
End Function
I tested this with 2 sheets allocated into an array. You should be able to add additional sheets with their respective names.
EDIT: The function should use the cell addresses as the strings passed into it.
=ACosts("B15", "C15")
A valid cell reference passed into a function ACosts
is useless because the string value of cell references is used in many sheets.
source to share