Combine two udfs into an array
 

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

--
Message posted from http://www.ExcelForum.com

Combine two udfs into an array
 

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 < wrote:
the first evaluates a column of text and give a conditional sum of the
column to the right, then next udf gives a contitional sum of the next
column to the right

I want to highlight one or two cells and the range to be evaluated (as
I do now with both of these udf) but I want the answer to first udf in
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 to
make it an array formula


---
Message posted from http://www.ExcelForum.com/

Combine two udfs into an array
 

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

--
Message posted from http://www.ExcelForum.com

Combine two udfs into an array
 

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 < wrote:
im doing this peace meal so I can figure out how it actually works. If
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 each
time. I have to analyze bout 300 of the little groups, with the udf I
only have to input one argument (range).

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


---
Message posted from http://www.ExcelForum.com/

Combine two udfs into an array
 

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

--
Message posted from http://www.ExcelForum.com

Combine two udfs into an array
 

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

--
Message posted from http://www.ExcelForum.com

Combine two udfs into an array
 

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

Alan Beban

ksnapp < wrote:
when i run this udf it highlights the arr = part at the end and a
message box says cant assign to array


---
Message posted from http://www.ExcelForum.com/

Combine two udfs into an array
 

Using Excel 97?

if so, change
Dim arr()

to

Dim arr

--
Regards,
Tom Ogilvy

"ksnapp " wrote in message
...
when i run this udf it highlights the arr = part at the end and a
message box says cant assign to array


---
Message posted from http://www.ExcelForum.com/

Combine two udfs into an array
 

Duh! Thanks, Tom.

Alan Beban

Tom Ogilvy wrote:
Using Excel 97?

if so, change
Dim arr()

to

Dim arr

Combine two udfs into an array
 

it works its perfec

--
Message posted from http://www.ExcelForum.com