Hello. friends.
I have a column with the following details:
Month
------
JAN
FEB
FEB
JAN
MAR
As you see appear just 3 months.
When I select this range aboth, I want to show "3" in a status bar. Is any way to do this?
If it impossible, what the effective macros to show it in a message box?
Thanks
Re: Custom function in a status bar
here's one way, assuming Range B2:B10 contains your list, this will place the number of unique ites in A1 then in the Statusbar
'---------------------------------------------------------------------------------------
' Module : Module1
' DateTime : 22/08/2005 19:16
' Author : royUK
' Website : www.excel-it.com
' Purpose :
'---------------------------------------------------------------------------------------
Option Explicit
Sub GetUnique()
Range("a1").Formula = "=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1)) "
Application.StatusBar = "The range contains " & Range("a1").Value & " unique items"
End SubRe: Custom function in a status bar
Thank you, royUK.
Very nice & useful solution.
Thanks
Re: Custom function in a status bar
You can replace Application.StatusBar with msgbox
Re: Custom function in a status bar
royUK,
A2:A10 is a static range, how to define in this formula the following range:
#
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
#
I put "selection" in a formula but it doesn't work.
Re: Custom function in a status bar
Do you mean like this
'---------------------------------------------------------------------------------------
' Module : Module1
' DateTime : 23/08/2005 19:16
' Author : royUK
' Website : www.excel-it.com
' Purpose : amend to include dynamic range
'---------------------------------------------------------------------------------------
Option Explicit
Sub GetUnique()
Dim MyRange As Range
Set MyRange = Range("a2", Range("a65536").End(xlUp))
Range("a1").Formula = "=SUM(IF(FREQUENCY(MATCH(MyRange,MyRange,0),MATCH(MyRange,MyRange,0))>0,1)) "
Application.StatusBar = "The range contains " & Range("a1").Value & " unique items"
End SubDon’t have an account yet? Register yourself now and be a part of our community!