double quote usage with Evaluate function

mobygrid · Oct 21st 2010

Hi Everyone,
I had lots of help over years, so thanks to all contributers, I am trying to feed the forum back in these days;
Below small piece of code does not work, any idea?
thanks

Code

MsgBox Evaluate("=CountA(SourceWB.Worksheets(1).range(""B:B""))")

neither this;

Code

MsgBox Evaluate("=CountA(SourceWB.Worksheets(1).range("B:B"))")

Rob Xaos · Oct 21st 2010

Re: double quote usage with Evaluate function

The Evaluate method only works for Excel formulas as they would be entered directly into an Excel cell. You are mixing the use of an Excel formula using COUNTA with VBA syntax. You need:

Code

MsgBox Evaluate("COUNTA('" & SourceWB.Worksheets(1).Name & "'!B:B)")

or

Code

MsgBox WorksheetFunction.CountA(SourceWB.Worksheets(1).Range("B:B"))

**mikerickson** · Oct 21st 2010

Re: double quote usage with Evaluate function

Try

Code

MsgBox Evaluate ("=COUNTA" & SourceWB.Worksheets(1).Range("B:B").Address(,,,True))

mobygrid · Oct 21st 2010

Re: double quote usage with Evaluate function

Thanks Rob, great help! Especially the first one is very interesting. appreciate that.

mobygrid · Oct 21st 2010

Re: double quote usage with Evaluate function

You are opening my vision vision.. thanks a lot..

double quote usage with Evaluate function

Participate now!

Share