Combine two udfs into an array

[ksnapp]

the first evaluates a column of text and give a conditional sum of th
column to the right, then next udf gives a contitional sum of the nex
column to the right

I want to highlight one or two cells and the range to be evaluated (a
I do now with both of these udf) but I want the answer to first udf i
the active cell, and the answer to the second in the cell to its right

here is the first udf

Function asdf(myRange As Range) ' UDF to give total tran count
Dim T As Double
Dim myCell As Range
T = 0
For Each myCell In myRange
CaseText = myCell.Value
Select Case (CaseText)
Case Is <> "TOTAL ACCOUNTS"
T = T + myCell.Offset(0, 1).Value
End Select
Next myCell
asdf = T
End Function

here is the second

Function qwer(myRange As Range) ' UDF to give total tran count
Dim T As Double
Dim myCell As Range
T = 0
For Each myCell In myRange
CaseText = myCell.Value
Select Case (CaseText)
Case Is <> "TOTAL ACCOUNTS"
T = T + myCell.Offset(0, 2).Value
End Select
Next myCell
asdf = T
End Function

as you can see there almost identical I just have no idea on how t
make it an array formul

 

[Alan Beban]

Doing this piecemeal wastes time. Your previous posting indicated you
wanted the result in a Message Box; now you're saying you want it in two
adjacent cells on the worksheet. Why do you think you want an array
formula? Why not just:

In one cell =SUMPRODUCT(($E3:$E6="TOTAL ACCOUNTS")*(F3:F6)) filled
across to the next cell

Alan Beban

 

[ksnapp]

im doing this peace meal so I can figure out how it actually works. I
I present a big problem I get a great answer that I can't figure out.
thats why the first message about the msg box.

I don't wanna use sum product becase i have to specify 2 ranges eac
time. I have to analyze bout 300 of the little groups, with the udf
only have to input one argument (range).

I don't really know if an array is what I need, im just hoping to fil
both of the cells with the data I need in one functio

 

[Alan Beban]

I don't know enough about what you're actually needing to accomplish to
have a view about how efficient this might be, but unless I'm
misunderstanding the structure of your data, the following will do what
you want:

Function asdf(myRange As Range)
Dim T As Double
Dim myCell As Range
Dim arr()
T = 0
U = 0
For Each myCell In myRange
CaseText = myCell.Value
Select Case (CaseText)
Case Is <> "TOTAL ACCOUNTS"
T = T + myCell.Offset(0, 1).Value
U = U + myCell.Offset(0, 2).Value
End Select
Next myCell
arr = Array(T, U)
asdf = arr
End Function

Alan Beban

 

[ksnapp]

that is gonna do the trick and its good code to disect thank yo

 

[ksnapp]

when i run this udf it highlights the arr = part at the end and
message box says cant assign to arra

 

[Alan Beban]

Then you changed it from what I posted; probably the declaration of
arr(). Post your exact code.

Alan Beban

 

[Tom Ogilvy]

Using Excel 97?

if so, change
Dim arr()

to

Dim arr

 

[Alan Beban]

Duh! Thanks, Tom.

Alan Beban

 

[ksnapp]

it works its perfec