code fails once then works...How is that possible?

[Chrissirhc]

Hi,

This code

Application.CountIf(rngControl.Rows(intRow), True)

fails with run-time error '13': Type mismatch. However, when I run the line again by hitting F8, the code works fine.

How is that possible?

Thanks,

Chris

 

[GlennUK]

If you had screenupdating off, the screen would update when the code broke with the run-time error, altering some aspects of some objects. Would that affect anything being done in your code?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Chrissirhc]

Sorry, I don't understand. Are you saying screen updating should be on all the time to prevent these kinds of errors?

Thanks in advance,

Chris

 

[GlennUK]

I'm saying that sometimes it does, and sometimes it doesn't, it all depends on what you've programmed ( or even how you've programmed it ). Have you tried running your code with screenupdating = True?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Chrissirhc]

Seemed to be an external object issue. Strange how the debug line feel on that isn't it?

 

[vbap]

Curious whether what GlenUK said worked?

I'm a little perplexed. Which MS Office application are you working with? Excel? The Application object doesn't have a countif method in my version of Excel 2000. I get a compile error when I try the code in my VBE.

Are you trying to return the count of cells in the range that have the value -1 or negative number in them?

Are you trying to use an Excel Macro builtin worksheet function? Try:

Application.ExecuteExcel4Macro("Book1!My_Macro()")

I've not used this method before but look up. It might be helpful.

 

[GlennUK]

hi vbap, I'd like to know about the screenupdating change working or not too.


As for Countif, it works fine on my Excel 97. It's a worksheet function, and can be accessed by Application.Countif or Application.WorksheetFunction.Countif in my version. Maybe later versions have got a more rigid language syntax, and only the latter works.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Chrissirhc]

I didn' change screenupdating. I changed a server I was pointing to and it fixed it. It looks like an object that conects to the server didn't like something the server was giving it, but didn't know how to give a useful error and confused Excel.

I know a very fluffy answer... But unfortunately I can't allocate the time to find out exactly what happened at the moment.

Thanks for your help.

 

[vbap]

GlennUK

Turns out application.worksheetfunction.countif still available in Excel 2000. I apparently missed it while looking under help. Application.countif doesn't seem to be available.

 

[GlennUK]

I thought that Application.Countif was still available under Excel 2000, even though it doesn't say so in the help. It doesn't really matter ... it's just less typing

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.