469,159 Members | 1,481 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,159 developers. It's quick & easy.

Excel 2000 Function problem

63
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!
Sep 7 '10 #1
10 1209
Guido Geurs
767 Expert 512MB
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.
Sep 7 '10 #2
grego9
63
Just tried to attach it - it says it is an invalid file - It's 3MB in size is that likely to be the issue?
Sep 7 '10 #3
Guido Geurs
767 Expert 512MB
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.
Sep 7 '10 #4
kadghar
1,295 Expert 1GB
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:

Expand|Select|Wrap|Line Numbers
  1. Function StCashTotalGBP(Managed, Entity) as Double
  2.     Dim a as Variant
  3.     a = Range(Cells(1,1), Cells(3,3))
  4.     'or whatever the rank is
  5.     StCashTotalGBP = 0
  6.     Dim counter as Integer
  7.     For counter = 1 to Ubound(a)
  8.         if a(counter,5) = Managed and a(counter,6) = Entity Then StCashTotalGBP = StCashTotalGBP + a(counter,80)
  9.     Next
  10. 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)
Sep 8 '10 #5
grego9
63
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!
Attached Files
File Type: zip NEW Facility Headroom WIP.zip (560.0 KB, 39 views)
Sep 8 '10 #6
Guido Geurs
767 Expert 512MB
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)
Attached Images
File Type: gif function list 2.GIF (9.0 KB, 114 views)
Attached Files
File Type: zip NEW Facility Headroom WIP_v2.zip (543.3 KB, 43 views)
Sep 8 '10 #7
kadghar
1,295 Expert 1GB
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.

Expand|Select|Wrap|Line Numbers
  1. with worksheets("sheet2")
  2.     a = Range(.cells(1,2), .cells(3,4))
  3. end with
that'll do
Sep 8 '10 #8
grego9
63
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!
Sep 9 '10 #9
Guido Geurs
767 Expert 512MB
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.
Attached Files
File Type: zip NEW Facility Headroom WIP_v3.zip (543.3 KB, 36 views)
Sep 9 '10 #10
grego9
63
Perfect - Thanks for your persistence! You have saved me from insanity!
Sep 9 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Dean Bortell | last post: by
reply views Thread by Dent | last post: by
1 post views Thread by Steven | last post: by
1 post views Thread by Randall Arnold | last post: by
reply views Thread by NewC#User | last post: by
reply views Thread by jinu | last post: by
1 post views Thread by =?Utf-8?B?R2xlbm4gR29tZXo=?= | last post: by
2 posts views Thread by smtwtfs007 | last post: by
reply views Thread by smtwtfs007 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.