converting sub routine to function in excel 1

[JCrin]

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 = "a22"
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
--------------------------------------------------------

 

[SkipVought]

Hi,

Function sumifall(Look_Val As String, Tble_Array As String, Sum_Range As String)
    'Look_Val = "a12"
    'Tble_Array = "a5:d5"
    'Sum_Range = "a2:D2"
    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
    
    sumifall = holdit

End Function

Skip,

_[red][/red]

 

[JCrin]

Thanks, but this still returns #VALUE! when I execute the function...anyone know why? I assume variable mismatching, or something is going on...

 

[SkipVought]

Hey, you're gonna have to do some debugging!

Put some breaks in, step thru, use the Watch Window and see hat your code is doing!

Skip,

_[red][/red]

 

[JCrin]

I'd love to, but, like I said, it works fine as a sub routine..Can you step through a function?? If not, I don't know what else to do...

Thanks.

 

[SkipVought]

"Put some breaks in, step thru, use the Watch Window and see what your code is doing!"

You can do that by ...

1. insert a break early in your function

2. editing the cell that your function is in

3. begin stepping.

Skip,

_[red][/red]

 

[PHV]

Did you call your function with a formula like this ?
=sumifall("a12", "a5:d5", "a22")

I ask because you defined the parameters as string (instead of range).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JCrin]

Thanks for the responses...I've narrowed the issue down to one line of code:

Loop While Not c Is Nothing And c.Address <> firstaddress

This works fine in the subrountine but kicks out in the UDF. I believe the reason is the line above it:
Set c = .FindNext(c)

This seems to fail in the function, while working just fine in thesub...this is really odd, as I'm using the same parameters in both instances....anyone have any ideas?

 

[SkipVought]

I just started looking at your logic, and upon review, the question arises, "why are you not using the SUMIF function on the sheet?"

Skip,

_[red][/red]

 

[SkipVought]

...oops.

But then I realize that you are looking a multiple sheets (a malady that occurs when a workbook is poorrly designed).

That aside, try this statement rather than FindNext...

Set c = .Find(b, c)

Skip,

_[red][/red]

 

[JCrin]

That did it. Thank you so much for your help!

 

[SkipVought]

You may not be getting the value you expect IF the value you are searching for is the FIRST cell in Tble_Array. Test to make sure.


Skip,

_[red][/red]