473,395 Members | 1,530 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

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 1361
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, 55 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, 118 views)
Attached Files
File Type: zip NEW Facility Headroom WIP_v2.zip (543.3 KB, 63 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, 54 views)
Sep 9 '10 #10
grego9
63
Perfect - Thanks for your persistence! You have saved me from insanity!
Sep 9 '10 #11

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

Similar topics

3
by: Dean Bortell | last post by:
This code runs fine on win xp and office xp: string sheetPassword = "Senior1993"; string sheetToOpen = "NewRpt1c.xls"; Excel.Application excelApp = new Excel.Application();...
0
by: Dent | last post by:
I am having a problem with a program that I wrote to format an Excel spreadsheet. I wrote the program on a WinXP/Office XP/Visual Studio .NET 2003 computer. I am using late-binding. When I try...
1
by: Steven | last post by:
Hello, I use the following code to make an Excel-file, which works fine with Excel 2003 (and the MS Excel 11.0 object library): Dim Excel As New Excel.Application Dim oBook As...
1
by: Randall Arnold | last post by:
I'm converting a vbscript program to vb.net. Witht he exception of .net idiosyncrasies, most of it is working well with the same code. My only problem is that some properties and methods are...
0
by: NewC#User | last post by:
Hi Everyone, I am using TextToColumns to split strings to seperate columns in C#. It works fine with 14 arguments for Excel 2003, but getting running error for Excel 2000. I think the problem is...
0
by: jinu | last post by:
When you save an excel from a vb.net application, throws error object reference not set to an instance of an object. if it is not excel 2000, program is ok only if excel 2000 installed, this...
6
by: grego9 | last post by:
I am trying to write some code to get Excel 2000 to insert a blank row wherever there is a "NO" in column O (above the NO). The code below nearly does this - but not quite!. The problem I have is...
1
by: =?Utf-8?B?R2xlbm4gR29tZXo=?= | last post by:
Does Office Excel 2000 support XSLT transformation of data from XML cause am having problem when the attachment is opened in the client side if the Excels version is in Office 2000 and also its...
2
by: smtwtfs007 | last post by:
Guys, I am having problems with Excel 2000 to Excel 2003 dll in production. I am having version 2003 in my PC and I used the below code change to open password protected excel files in my...
0
by: smtwtfs007 | last post by:
Guys, I am having problems with Excel 2000 to Excel 2003 dll in production. I am having version 2003 in my PC and I used the below code change to open password protected excel files in my...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

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.