WorksheetFunction Calculation Error

errorWS.Range("B3").Value = Application.WorksheetFunction.CountA("A5:A" & 
errorWS_startRow - 1)

 The above line of code is always 1, even when errorWS_startRow is much > 1, 
i.e:
errorWS_startRow=144, 749 etc.

Any ideas what is going on?
0
Utf
3/9/2010 7:16:02 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
966 Views

Similar Articles

[PageSpeed] 50

I'd suggest including the sheet reference for the CountA; something like this 
(untested)

errorWS.Range("B3").Value = 
Application.WorksheetFunction.CountA(errorWS.Range("A5:A" & 
 errorWS_startRow - 1))

"Ayo" wrote:

> errorWS.Range("B3").Value = Application.WorksheetFunction.CountA("A5:A" & 
> errorWS_startRow - 1)
> 
>  The above line of code is always 1, even when errorWS_startRow is much > 1, 
> i.e:
> errorWS_startRow=144, 749 etc.
> 
> Any ideas what is going on?
0
Utf
3/9/2010 7:51:01 PM
If the cells from A5 to A & errorWS_startRow-1 are empty, you will get 
either 1 or zero depending on whether there is data in the startRow - 1 or 
not.  CountA only counts cells with data that has a value.  It will not 
count empty cells but does count cells with zero,  ""  or  ' characters that 
are hidden because they have values.  So, the range you are testing must be 
empty except for one cell.



"Ayo" <Ayo@discussions.microsoft.com> wrote in message 
news:601B0838-D550-4E26-8859-C9B2335254FD@microsoft.com...
> errorWS.Range("B3").Value = Application.WorksheetFunction.CountA("A5:A" &
> errorWS_startRow - 1)
>
> The above line of code is always 1, even when errorWS_startRow is much > 
> 1,
> i.e:
> errorWS_startRow=144, 749 etc.
>
> Any ideas what is going on? 


0
JLGWhiz
3/9/2010 7:55:22 PM
Reply:

Similar Artilces:

Workbook Merge Fails with a Temporal-like error
All of the basic settings are fine because simple merges are working. When I make one change is Spreadsheet A, and Merge those changes into Spreadsheet B, everything works fine. However, when I make changes in Spreadsheet A, and another user makes changes in Spreadsheet B (which is an exact copy of Spreadsheet A), I get the following error: ------ That workbook cannot be merged because it contains changes that have not been merged followed by later changes that have already been merged into the current workbook. ------ Any ideas? Nevermind, I found the solution. -techgroups &g...

Detailed Sales Report
On the detailed Sales Report the Profit Margin column for the grouped items is calculated based on the average profit margin of each item sold in that group. The overall profit margin is calculated based on the average of all profit margins of all items sold. Is there a way to have the grouped profit margin values recalculated based on actual profit and total sales? We are finding that the average profit margin calculation is not accurate when compared to re-calculating the profit margin manually using the Profit and Total Sales columns. We would like the values to be consistent. W...

Use different formula to calculation based on conditions
Hi. It's the look of my Excel file: Item 1----Amount Category---Details: ... Item 2----Amount Category---Details: ... Item 3----Amount Category---Details: ... Item 4----Amount Category---Details: ... As you see: - A1 is the item's name; A2 is the price/amount/cost/value of the item - B1 is the category the item belongs to; B2 is the description of the item. Now I would like to do some math which are the following: 1) If the category type is income, add all of them 2) If the category type is one-off expense, add all of them & put a minus sign in front of the value 3) If the cat...

smtp error in event viewer
reads "the remote host responded to the smtp command 'rcpt' with '451 1169751691' ". this occurs when sending mail to serveral domains. Any idea what this is trying to tell me? Thank you. In news:2B952C06-0E52-4316-883A-9654C8A30C28@microsoft.com, lawfirmit <lawfirmit@discussions.microsoft.com> typed: > reads "the remote host responded to the smtp command 'rcpt' with '451 > 1169751691' ". this occurs when sending mail to serveral domains. Any > idea what this is trying to tell me? Thank you. Did the sender get an NDR? If so...

Syntax Error 02-07-08
I am having a problem with the syntax for the following code. '''If specified a Work Area If Not IsNothing(Me.cboWorkArea) Then varWhere = (varWhere + " AND ") & "[WorkArea] LIKE '*" & Me.cboWorkArea & "*'" End If '''If specified a Location If Not IsNothing(Me.cboLocation) Then varWhere = (varWhere + " AND ") & "[Location] LIKE '*" & Me.cboLocation & "*'" End If Each varwhere get their values from a combo box. The codes actua...

Entry Point Error WHAT DOES THIS MEAN?? FIX??
This pops up when I open my outlook 2003 for XP CiceroUIWnd Frame: OUTLOOK.EXE-Entry Point Not Found X The procedure entry point_GetIUMS@4 could not be located in the dynamic library MSDART.DLL. WHAT DOES THIS MEAN I checked on Microsoft's DLL Helpbase (you may need to fix the wrap on the link below to use it): http://support.microsoft.com/default.aspx?scid=/servicedesks/fileversion/dllinfo.asp&SD=TECH&FR=0 and I found that the MSDART.DLL is: Microsoft Data Access - OLE DB Runtime Routines Not much help, but definately something to do with a database... Then I looked...

Error message when running Exchange Server ActiveSync Web Admin To
Hi. I have installed the Exchange Server ActiveSync Web Admin Tool (on our only Exchange server) and I can log on to it just fine but when I enter the mailboxname to lookup list of devices and click "continue", I get the following error message: The remote server returned an error: (403) Forbidden. [WebException: The remote server returned an error: (403) Forbidden.] System.Net.HttpWebRequest.CheckFinalStatus() +680 System.Net.HttpWebRequest.EndGetRequestStream(IAsyncResult asyncResult) +74 System.Net.HttpWebRequest.GetRequestStream() +234 Microsoft.Exchange.AirSyn...

VBA error #2
Can anyone tell me why when I run the following code, I get the "End If without block If" Error? Set r = Range("Name") For n = 1 To r.Rows.Count If IsEmpty(r.Cells(n, 1)) Then n.Activate ActiveCell.Value = a GoTo Option3 End If Next n Option3: Sheets("Sheet1").Activate On Mon, 6 Sep 2004 18:49:30 -0700, "mark1" <anonymous@discussions.microsoft.com> wrote: >Can anyone tell me why when I run the following code, I >get the "End If without block If" Error? > >Set r = Range("Name") >For n = 1 To r.R...

e-Mail RM Statements 'Unknown Error'
Hi Folks GP Ver 10. My client was able to e-mail RM Statements last month but this month the Status report is failing every one and the error as per the report under the 'Status' column is 'Unknown Error. Now that's not the most useful error message I've seen so I'm wondering if other folk have come across it and what they found to be the problem. Any suggestions would be welcome. Thankx Sheila ...

New hotfix for event error
A number of users have reported this error in their CRM application event logs where it says cannot clean up activitybase table. Microsoft now has a hot fix for this issue as per this article TechKnowledge Microsoft CRM Deletion Service Failure Error "Cannot Clean Up ActivityBase Table" in the Application Event Log on the Microsoft CRM Server Return to previous page Document ID: 30380 Date Created: 4/24/2003 Date Last Modified: 12/8/2003 3:56:45 PM Language: English - United States C...

set associations error
I'm getting the error "This file does not have a program associated with it for performing this action. Create an association in the Set Associations control panel." Now, I've browsed this forum a bit, and I learned the Set Associations panel is for setting the default program for opening a file type. But my issue is this: I'm getting this error message when I tried to open up a computer game (specifically, Sacred.exe). What should I set to open up .exe programs? I've looked on the Set Associations panel, and I don't see .exe on the list of file types...

Integration Manager / Business Portal Error
Hi While tyring to install Integration Manager for GP 10 and Business Portal for GP 10, i am getting the following error "Object Reference not set to an instance of an object". Hence, i am not able to install IM and Business Portal. I have installed SP2 for GP in the system. Since at the time of installation itself this error message appears, i cannot apply any hotfix / Sp for IM and Business Portal. Can anybody help me out.... -- Narayanan K L Hi Narayanan Have you looked at Article ID : 938461 or Article ID : 942750 on Customer/Partner Source? Fliehigh "Na...

non-numeric in a calculation
I need to calculate hours worked for the employees. The staff reg got the moths dates in row 1, and "time in" and "time out" in row 2. Column A is for all the staff names. Say employee Harmse worked the following shifts for Jan. on the 1st Jan (B2) started 7 and fin 17(C2) on the 2nd Jan also (D2) 7 till 17(E2) but on the 3rd Jan (F2) "AWOL" and "AWOL"(G2)on the 4thJan (H2)"SICK"and "SICK"(I2). What formula will I use to calculate total hours worked for the month. We used to replace the text with 0 and used a simple form...

crm 3.0 : authentication error
Hi, I get the authentication error prompt for each user having the same login username in the internal domain than in the CRM domain. Passwords are not the same in each domain, and there seems to be some kind of automatic authentication while loading the page in IE. Is there any setting, which could help me prevent this ? thanks, @ntonio By default, the IIS web site allowed anonymous connections. I disabled them, allowing only integrated authentication. It now works fine! @ntonio ...

When trying to load microsoft Outlook error message saying: unabl.
djchampkin <djchampkin@discussions.microsoft.com> wrote: Is there a question here somewhere? -- Brian Tillman Sorry sent twice. "Brian Tillman" wrote: > djchampkin <djchampkin@discussions.microsoft.com> wrote: > > Is there a question here somewhere? > -- > Brian Tillman > ...

Looking for a simple SOP Freight Calculator
We want to automatically calculate freight charges in SOP based on a % of invoice sales subtotal and shipping method. Is there an ISV product that does this? Thanks. Check this out: http://products.bluemoonind.com/productDetails.php?id=2 "Lou" wrote: > We want to automatically calculate freight charges in SOP based on a % of > invoice sales subtotal and shipping method. Is there an ISV product that > does this? > Thanks. ...

Move mailbox error
Hi I am trying to move mailboxes from an Exchange 5.5 cluster (NT domain) to a Exchange 2003 cluster (W2k3 domain) all in the same site. There is a ADC in place. When trying to move a mailbox I get the following error in the resulting xml file in the source server section errorCode 0xc1010a91 and the move fails. Does anyone have any ideas where i can start troubleshooting this. Thanks Crispy ...

This error usually appears if the OST or PST file you are using is
I didnt know if this was an Exchange issue or an Outlook 2003 issue, I thought Id ask here. I am running Exchange 2003 and Outlook 2003 on the client side. I have users sharing a group mailbox, some 10 people. However, I have two new users who I set up and have added this shared mailbox to outlook for them as a secondary mailbox, gets the following message when I try to open it... ____________________________________________________________________ This error usually appears if the OST or PST file you are using is: · Unavailable · Protected with file permissio...

Address errors
I have 10 e-mails to send. The addresses look good but I get two errors. Sometimes they will all send but one will come back undeliverable. So I find the URL is good but the name is bad. Then with another group of 10 I hit 'SEND' and is says an address is immediately rejected. It may or may not tell me which is bad. The message is removed from the Outbox. Did I send the other 9? What is wrong with the address? What does the 'Tools' . 'Check names' do? Does it just check against the address book or does it check the validity of the addresses against internet URL...

The recipient name is not recognized error 553 Spam
I am getting this message: The recipient name is not recognized The MTS-ID of the original message is: c=US;a= ;p=MVCHAMBER;l=MVC5-040409002841Z-868 MSEXCH:IMS:MVCHAMBER:MVCHAMBER:MVC5 3553 (000B09B6) 553 64.168.156.* mail rejected due to excessive spam when trying to send from this server. Does anyone have any idea why? Did you change the actual IP address to 64.168.156.* or is that how the message reads? It seems someone is blocking an entire Class C of PacBell internet's range, as a source of spam. See if your IP address is on an IP blocklist: http://www.ordb.org/lookup/ ...

Project Server encountered a problem with this error code: 1028 Er
Hi, Im having this message in the red triangle with two projects. Any idea of what it might be? Project Server 2007 Thank you On 4 Jan, 19:04, Filipe Carqueja <Filipe Carqu...@discussions.microsoft.com> wrote: > Hi, > > Im having this message in the red triangle with two projects. > Any idea of what it might be? > > Project Server 2007 > > Thank you Error 1028: project does not exist (project server 2007) ...

Ntuser.dat error
I have a user who when opening Excel receives Cannot access "NTUSER.DAT" and Cannot access "NTUSER.DAT.LOG" error messages. Everything else seems to work fine. Any ideas? Mike NTUSER.DAT concerns profiles. Each user has an NTUSER.DAT file. Some google search threads with some info and a couple of fixes. http://snipurl.com/5ge4 You could try re-registering Excel first. Start>Run "excel.exe /unregserver"(no quotes and note the space before the / mark). Start>Run "excel.exe /regserver"(no quotes and note the space before the / mark). You ma...

Calendar Sudden Error
Hello Access Community, We have A2003 and have been using the calendar control forever without any problem. All of a sudden whenever the calendar is opened, we get the following error: Method 'Value' of object '_CustomControl' failed. If I click on the debug button, the code errors at the following line: mctl.value = Atx_Calendar.Value mctl is defined as an access control No one has touched this! We have several databases and it seem to be a problem in every one of the databases. I even tried creating a new database and the same thing happened. I checked the referenc...

Need help with find and calculate
Ok, scratching a bald spot on my head, and still nothing. I have 5 sheets on a work book. I need to find all values that =2575 in colum A. When it finds the values if they exist, sum the specified cell fro all pages. To better explain, 2575 is a billing number, the sheets ar timesheets. I want to find everyone who listed 2575 as a billin number. If it is listed, have the formula go across about 12 cells an find total hours for that billing number. I will have a summary shee at the end to list specific billing numbers, 2575, 3123,3124, and s on, with sum of all hours beside it. Can some please ...

error message when browsing ie
hi! i have a problem with this error message: windows internet explorer cannot find "http:// and sights written " make sure the path or internet address is correct. it shows every time when i open internet explorer and try to search.and it keeps opening another window with this message and another... and when i press "OK" or "X" it opens another internet explorer homepage. please help me to safe this annoying problem thanks Always state your IE version and full Windows version (e.g., WinXP SP3; Vista 64-bit SP2; Win7 RC; Win7) when posting...