Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Named Range count
Howdy
When I do a count of a dyanmic named range I get 1, when I do it for a static named range I get the correct count of the range. This is my code: Private Sub UserForm_Initialize() Dim DirectorateCount As Long Dim rgDirectorateListSource As Range Set rgDirectorateListSource = Worksheets("Data-Directorates").Range("DirectorateRange") DirectorateCount = rgDirectorateListSource.count End Sub Do I need to use a ByVal Target as RANGE somewhere or what am I doing wrong? Thanks Matt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Named Range count
I do not see anything wrong. Is your named range DirectorateRange correctly
specified? -- Cheers Nigel "Matt Jensen" wrote in message ... Howdy When I do a count of a dyanmic named range I get 1, when I do it for a static named range I get the correct count of the range. This is my code: Private Sub UserForm_Initialize() Dim DirectorateCount As Long Dim rgDirectorateListSource As Range Set rgDirectorateListSource = Worksheets("Data-Directorates").Range("DirectorateRange") DirectorateCount = rgDirectorateListSource.count End Sub Do I need to use a ByVal Target as RANGE somewhere or what am I doing wrong? Thanks Matt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Named Range count
It is defined like this
=OFFSET('Data-Directorates'!$A$1,0,0,COUNTA(DataDirectorates!$A: $A),1) Cheers Matt "Nigel" wrote in message ... I do not see anything wrong. Is your named range DirectorateRange correctly specified? -- Cheers Nigel "Matt Jensen" wrote in message ... Howdy When I do a count of a dyanmic named range I get 1, when I do it for a static named range I get the correct count of the range. This is my code: Private Sub UserForm_Initialize() Dim DirectorateCount As Long Dim rgDirectorateListSource As Range Set rgDirectorateListSource = Worksheets("Data-Directorates").Range("DirectorateRange") DirectorateCount = rgDirectorateListSource.count End Sub Do I need to use a ByVal Target as RANGE somewhere or what am I doing wrong? Thanks Matt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Named Range count
My apologies
Typos were the problem, as can be seen in name definition formula. Cheers Matt "Matt Jensen" wrote in message ... It is defined like this =OFFSET('Data-Directorates'!$A$1,0,0,COUNTA(DataDirectorates!$A: $A),1) Cheers Matt "Nigel" wrote in message ... I do not see anything wrong. Is your named range DirectorateRange correctly specified? -- Cheers Nigel "Matt Jensen" wrote in message ... Howdy When I do a count of a dyanmic named range I get 1, when I do it for a static named range I get the correct count of the range. This is my code: Private Sub UserForm_Initialize() Dim DirectorateCount As Long Dim rgDirectorateListSource As Range Set rgDirectorateListSource = Worksheets("Data-Directorates").Range("DirectorateRange") DirectorateCount = rgDirectorateListSource.count End Sub Do I need to use a ByVal Target as RANGE somewhere or what am I doing wrong? Thanks Matt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Named Range count
Matt,
As ever, it works fine for me. How do you define the dynamic range? -- HTH RP (remove nothere from the email address if mailing direct) "Matt Jensen" wrote in message ... Howdy When I do a count of a dyanmic named range I get 1, when I do it for a static named range I get the correct count of the range. This is my code: Private Sub UserForm_Initialize() Dim DirectorateCount As Long Dim rgDirectorateListSource As Range Set rgDirectorateListSource = Worksheets("Data-Directorates").Range("DirectorateRange") DirectorateCount = rgDirectorateListSource.count End Sub Do I need to use a ByVal Target as RANGE somewhere or what am I doing wrong? Thanks Matt |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Named Range count
Your formula refers to two separate sheets
Data-Directorates DataDirectorates -- Regards, Tom Ogilvy "Matt Jensen" wrote in message ... It is defined like this =OFFSET('Data-Directorates'!$A$1,0,0,COUNTA(DataDirectorates!$A: $A),1) Cheers Matt "Nigel" wrote in message ... I do not see anything wrong. Is your named range DirectorateRange correctly specified? -- Cheers Nigel "Matt Jensen" wrote in message ... Howdy When I do a count of a dyanmic named range I get 1, when I do it for a static named range I get the correct count of the range. This is my code: Private Sub UserForm_Initialize() Dim DirectorateCount As Long Dim rgDirectorateListSource As Range Set rgDirectorateListSource = Worksheets("Data-Directorates").Range("DirectorateRange") DirectorateCount = rgDirectorateListSource.count End Sub Do I need to use a ByVal Target as RANGE somewhere or what am I doing wrong? Thanks Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dynamic named range | New Users to Excel | |||
Dynamic Named Range | Excel Discussion (Misc queries) | |||
How to make a dynamic named range | Excel Discussion (Misc queries) | |||
Dynamic Named Range | Excel Discussion (Misc queries) | |||
Dynamic Named Range | Charts and Charting in Excel |