Array formula to code

[art]

Hello all:

I have the following formula to extract data from a long list. The list has
two columns, the first one with amounts, the second one with items. I use
this Array formula to give me in a list onlt the items that have an amount
next to it. I sense that this array is causing my workbook to work slow.
I am thinking now that maybe a custom funtion might work faster. Please let
me know if I am right. And if I am right, how to convert this formula to a
vba code.

=IF(ROWS(Sheet1!$1:1)<=COUNTIF(First,">0"),INDEX(First,SMALL(IF((ISNUMBER(First)*(First>0)),ROW(First)-ROW(Sheet1!A$1)+1),ROWS(Sheet1!$1:1)))&"

"&INDEX(Second,SMALL(IF((ISNUMBER(First)*(First>0)),ROW(First)-ROW(Sheet1!A$1)+1),ROWS(Sheet1!$1:1))),"")

"First" is a name range for the column with the amounts.
"Second" is the second column with the items in it.

Thanks

 

[Barb Reinhardt]

The easiest way to do this is to record a macro and "enter" the function
again. You really just need to commit the function again. End the macro
recorder and see what you have.

 

[art]

When I record, it just records the same formula i entered. I need a custom
function for this.

 

[Barb Reinhardt]

Do you want to add it to a series of cells programmatically, or do you want
to actually have a UDF to do it? FWIW, UDF's can sometimes be tricky to get
to recalculate.

Barb Reinhardt

 

[art]

I want to try UDF.

Do you want to add it to a series of cells programmatically, or do you want
to actually have a UDF to do it? FWIW, UDF's can sometimes be tricky to get
to recalculate.

Barb Reinhardt

 

[Charles Williams]

Hi Art,

Try this UDF, enter as a single 2-column by n-rows array formula, where n is
the max number of >0 items.
it will be a lot faster than your array formula for a large number of rows:
on my system for 250 rows of data it takes 0.004 seconds as opposed to over
10 seconds for the array formula.

Option Explicit

Function ArtList(theFirst As Range, theSecond As Range) As Variant
Dim vFirst As Variant
Dim vSecond As Variant
Dim oRng As Range
Dim j As Long
Dim k As Long
Dim vArr() As Variant
Dim nRows As Long
'
' get the data, allowing for whole-column refs
'
Set oRng = Intersect(theFirst.Parent.UsedRange, theFirst)
vFirst = oRng.Value2
Set oRng = Intersect(theSecond.Parent.UsedRange, theSecond)
vSecond = oRng.Value2
'
' get number of rows in the array formula
'
nRows = Application.Caller.Rows.Count
'
' setup result array
'
ReDim vArr(1 To nRows, 1 To 2)
'
' look for numeric>0 in First
'
k = 0
For j = 1 To UBound(vFirst)
If IsNumeric(vFirst(j, 1)) Then
If vFirst(j, 1) > 0 Then
k = k + 1
If k > UBound(vArr) Then Exit For
vArr(k, 1) = vFirst(j, 1)
vArr(k, 2) = vSecond(j, 1)
End If
End If
Next j
'
' output the result as a 2-column n Row array
'
ArtList = vArr
End Function

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[art]

Sorry for the late reply. Thanks for this function, but it does not seem to
work. I entered the function in amodule, and then in the excel sheet the
function, selecting in the first variable "first" and in the second variable
"second". but a "1" is the result without the item, and when I drag it to the
whole column, all of them are the same. Please help me finish this, I need it
desperately. Thanks.

 

[Charles Williams]

It is a multi-cell array formula, so you have to enter it into ALL the cells
at once

Go to the Visual Basic Editor (F11)
Insert Module
type in the function
go back to Excel and then

First - Select 2 columns wide and n rows deep
Second - type =ArtList(First,Second) in the formula box
Third - press Control-Shift-Enter
Fourth- if necessary press F9 to recalculate

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com