Hi! I wrote the sub below, which works, (it's a 3D SumIf) but when I make it a function it just returns VALUE#! Can someone tell me what I'm doing wrong?
Thanks!
-----------------------------------------------------------
Sub sumifall()
Dim Look_Val As String, Tble_Array As String, Sum_Range As String
'Look_Val = "a12"
'Tble_Array = "a5:d5"
'Sum_Range = "a2
2"
b = Range(Look_Val).Value
cd = Range(Sum_Range).Row
holdit = 0
For Each Wsheet In ActiveWorkbook.Worksheets
With Wsheet.Range(Tble_Array)
Set c = .Find(b, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
fd = c.Column
df = Wsheet.Cells(cd, fd).Value
holdit = holdit + df
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next Wsheet
Cells(1, 1) = holdit
End Sub
--------------------------------------------------------
Thanks!
-----------------------------------------------------------
Sub sumifall()
Dim Look_Val As String, Tble_Array As String, Sum_Range As String
'Look_Val = "a12"
'Tble_Array = "a5:d5"
'Sum_Range = "a2
b = Range(Look_Val).Value
cd = Range(Sum_Range).Row
holdit = 0
For Each Wsheet In ActiveWorkbook.Worksheets
With Wsheet.Range(Tble_Array)
Set c = .Find(b, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
fd = c.Column
df = Wsheet.Cells(cd, fd).Value
holdit = holdit + df
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next Wsheet
Cells(1, 1) = holdit
End Sub
--------------------------------------------------------