Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

converting sub routine to function in excel 1

Status
Not open for further replies.

JCrin

MIS
May 21, 2007
5
US
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: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
Cells(1, 1) = holdit

End Sub
--------------------------------------------------------
 



Hi,
Code:
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,

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



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,

[glasses] [red][/red]
[tongue]
 
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.
 




"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,

[glasses] [red][/red]
[tongue]
 
Did you call your function with a formula like this ?
=sumifall("a12", "a5:d5", "a2:D2")

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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?
 




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,

[glasses] [red][/red]
[tongue]
 




...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...
Code:
Set c = .Find(b, c)


Skip,

[glasses] [red][/red]
[tongue]
 
That did it. Thank you so much for your help!
 




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,

[glasses] [red][/red]
[tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top