472,103 Members | 1,824 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,103 software developers and data experts.

Excel 2000 Function problem

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

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 1254
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
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
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
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, 54 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, 116 views)
Attached Files
File Type: zip NEW Facility Headroom WIP_v2.zip (543.3 KB, 62 views)
Sep 8 '10 #7
1,295 Expert 1GB
Remember the Cells are objects inside a Worksheet
just call the cells of the worksheet you want:

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
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)

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, 53 views)
Sep 9 '10 #10
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.