COUNTA msgbox

[Daminc]

I'm not sure if this is possible but I have been trying to create a macro that would do a COUNTA function on a selected range and display the result in a message box

i.e.

Dim Rangea As Range
Dim Resulta As Variant

Rangea = selelected.Range
Resulta = Application.WorksheetFunctions.counta.Rangea
MsgBox Resulta

It doesn't come close to working so can anyone suggest a way of accomplishing this?

Cheers

[R.VENKATARAMAN]

try this

worksheetfunction.CountA(columns("B:B"))

fill some entries in columnB
and type this in immediate window
?worksheetfunction.CountA(columns("B:B"))
what do you get


"Daminc" <Daminc.1zrpzm_1134123902.1069@excelforum-nospam.com> wrote in
message news:Daminc.1zrpzm_1134123902.1069@excelforum-nospam.com...
>
> I'm not sure if this is possible but I have been trying to create a
> macro that would do a COUNTA function on a selected range and display
> the result in a message box
>
> i.e.
>
> Dim Rangea As Range
> Dim Resulta As Variant
>
> Rangea = selelected.Range
> Resulta = Application.WorksheetFunctions.counta.Rangea
> MsgBox Resulta
>
> It doesn't come close to working so can anyone suggest a way of
> accomplishing this?
>
> Cheers
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=492124
>

[chijanzen]

MsgBox WorksheetFunction.CountA(Selection)

--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"Daminc" wrote:

>
> I'm not sure if this is possible but I have been trying to create a
> macro that would do a COUNTA function on a selected range and display
> the result in a message box
>
> i.e.
>
> Dim Rangea As Range
> Dim Resulta As Variant
>
> Rangea = selelected.Range
> Resulta = Application.WorksheetFunctions.counta.Rangea
> MsgBox Resulta
>
> It doesn't come close to working so can anyone suggest a way of
> accomplishing this?
>
> Cheers
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=492124
>
>

[Jan Karel Pieterse]

Hi Daminc,

> Dim Rangea As Range
> Dim Resulta As Variant
>
> Rangea = selelected.Range
> Resulta = Application.WorksheetFunctions.counta.Rangea
> MsgBox Resulta
>

Close:

Dim Rangea As Range
Dim Resulta As Variant

Set Rangea = selelected.Range
Resulta = Application.WorksheetFunctions.counta(Rangea)
MsgBox Resulta

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

[Gordon Rainsford]

Dim result As Integer
result = WorksheetFunction.Counta(Selection)
MsgBox result


--
Gordon Rainsford

London UK


Daminc <Daminc.1zrpzm_1134123902.1069@excelforum-nospam.com> wrote:

> I'm not sure if this is possible but I have been trying to create amacro
that would do a COUNTA function on a selected range and displaythe
result in a message box
>
> i.e.
>
> Dim Rangea As Range
> Dim Resulta As Variant
>
> Rangea = selelected.Range
> Resulta = Application.WorksheetFunctions.counta.Rangea
> MsgBox Resulta
>
> It doesn't come close to working so can anyone suggest a way
> ofaccomplishing this?
>

[Norman Jones]

Hi Daminc,

You have several code suggestions. However, this information is available
directly in the report on the right-hand side of the status bar,


---
Regards,
Norman



"Daminc" <Daminc.1zrpzm_1134123902.1069@excelforum-nospam.com> wrote in
message news:Daminc.1zrpzm_1134123902.1069@excelforum-nospam.com...
>
> I'm not sure if this is possible but I have been trying to create a
> macro that would do a COUNTA function on a selected range and display
> the result in a message box
>
> i.e.
>
> Dim Rangea As Range
> Dim Resulta As Variant
>
> Rangea = selelected.Range
> Resulta = Application.WorksheetFunctions.counta.Rangea
> MsgBox Resulta
>
> It doesn't come close to working so can anyone suggest a way of
> accomplishing this?
>
> Cheers
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile:
> http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=492124
>

[Daminc]

Thanks for your replies.

There are several suggestions that work great here for what I wanted and a lot more food for thought in the bargain

I'm not sure where this info is though:
"However, this information is available directly in the report on the right-hand side of the status bar,"

[Jan Karel Pieterse]

Hi Daminc,

> I'm not sure where this info is though:
> -"However, this information is available directly in the report on the
> right-hand side of the status bar,"-

If you select some cells, the staus bar can show a number of statistics
(on the right hand side): SUM, COUNT, AVERAGE, MIN, MAX. Rightclick the
status bar to activate this or change the function used.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

[Norman Jones]

Hi Daminc,

Just to add to Jan Karels's response, if you do not see the Status Bar then:

Tools Options | View | Check 'Status Bar'

---
Regards,
Norman



"Daminc" <Daminc.1zrtoz_1134128702.8397@excelforum-nospam.com> wrote in
message news:Daminc.1zrtoz_1134128702.8397@excelforum-nospam.com...
>
> Thanks for your replies.
>
> There are several suggestions that work great here for what I wanted
> and a lot more food for thought in the bargain
>
> I'm not sure where this info is though:
> -"However, this information is available directly in the report on the
> right-hand side of the status bar,"-
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile:
> http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=492124
>

[Daminc]

Found it

Cheers guys