Excel 2000 Function problem

[grego9]

I have written the following function to total up the values in a column where the values in columns 5 and 6 of a range I have created (called "lori") are equal to the values on another summary tab (details lower down)

Function StCashTotalGBP(Managed, Entity)

Counter = 1: StCashTotalGBP = 0
Do While Counter <= Range("lori").Rows.Count
If Range("lori").Item(Counter, 5) <> Managed Then GoTo NextRow
If Range("lori").Item(Counter, 6) <> Entity Then GoTo NextRow

StCashTotalGBP = StCashTotalGBP + Range("lori").Item(Counter, 80): GoTo NextRow

NextRow: Counter = Counter + 1
Loop

End Function


I use this formula on the summary tab

=StCashTotalGBP(E10, D10)

E10 provides the "managed" value (which is in column 5 of the "lori" range) and D10 provides the "entity value " (which is in column 6 of the "lori" range)

When I enter this formula I get the #NAME? error. I know that the combination of Managed and entity exists in the range - so why is this not working?!

Is it because I need to specify that this range is on another tab to the summary tab? (NB the function is held at the This workbook level

Any help greatly appreciated!

[Guido Geurs]

Please is it possible to attach in BYTES an example of the XLS file?

Maybe its not the code but the format of Your data.
Its much easier for Us to detect the error than guessing what can be wrong.

[grego9]

Just tried to attach it - it says it is an invalid file - It's 3MB in size is that likely to be the issue?

[Guido Geurs]

Bytes let only some types to be attached (GIF, BMP, ...)
See list in attachment window.
You can only attach Excel file in ZIP files with max 5Mb.

[kadghar]

I wouldn't recomend you to use the range name, nor call the cells each time you need it.

If you define a variant, and asign a range to it, you'll have the variant as an array and it will be quite faster (really, over 100x faster)

So, why dont you try something like:

Function StCashTotalGBP(Managed, Entity) as Double
    Dim a as Variant
    a = Range(Cells(1,1), Cells(3,3))
    'or whatever the rank is
    StCashTotalGBP = 0
    Dim counter as Integer
    For counter = 1 to Ubound(a)
        if a(counter,5) = Managed and a(counter,6) = Entity Then StCashTotalGBP = StCashTotalGBP + a(counter,80)
    Next
Exit Function

Also you can add the range as a parameter, call it a, and that'll do with the same code (and remove the two first lines)

[grego9]

I have attached the file so you can see what is going on. I tried the Variant change but the range of cells that the data is held in is in a different tab to the where the main formula sits - so I would need the

a = Range(Cells(1, 1), Cells(484, 8))

formula to reference the other sheet name - how would I bake this in?

thanks for your continued help on this!

[Guido Geurs]

Your function must be in a module.
You can see if a function is accessible when You click on
"fx" in the menu bar (see GIF in attachment)

[kadghar]

Remember the Cells are objects inside a Worksheet
just call the cells of the worksheet you want:
Worksheets("sheet1").cells(1,1)

If you dont specify a worksheet it'll work on whatever active worksheet you have.

with worksheets("sheet2")
    a = Range(.cells(1,2), .cells(3,4))
end with

that'll do

[grego9]

Hi guys - thanks for your continued help on this. I made the relevant changes but I keep getting a sum of zero. In the file that ggeu uploaded it sums to 5. The actual result should be the sum of all the numbers in column H of the HYP-ST Cash tab where the criteria equal what's entered into the function on the summary tab. So in the example of ACTIVELY MANAGED and R-R plc this should sum to 62405. I am confused! Any further help greatly appreciated!

[Guido Geurs]

I'm sorry, my mistake, but in Your attachment there was "Item(Counter, 80)" and 80 is col="CB" so I entered in "CB" some values as test.

I have changed the code to 8 (col="H") and it's working (see attachment)

PS:
If You want to use these function a lot in a sheet, it's better to put the data in an array , collect the results in a 2nd array and finally dump the collected data in the appropriated sheet.

[grego9]

Perfect - Thanks for your persistence! You have saved me from insanity!