Counting entries from dynamic range

Anthropologista

New Member
Joined
Nov 5, 2017
Messages
10
Hello all,
I am writing a simple macro to count the number of categories in a dynamic range by:

  1. Offsetting the bottom of the range (“SectionInfant”) by -1
  2. CountA the resulting range (B4 to Target)
  3. Paste the resulting number in F1

I can’t get the following code to work. Any help is appreciated!

Code:
Sub Auto_Open()
 
Dim CurrentList As Integer
Dim Target As Range
 
Set Target = Range("SectionInfant").Offset(-1, 0)
CurrentList = Application.WorksheetFunction.CountA(Sheets("TOL Categories").Range(Range("B4", Target)))
Sheets("TOL Categories").Range("F1").Value = CurrentList
 
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,820
Office Version
365
Platform
Windows
In what way doesn't your code work?
 

Anthropologista

New Member
Joined
Nov 5, 2017
Messages
10
Fluff,
I get error 1004: Application-defined or object-defined error.

On the line that reads
Code:
CurrentList = Application.WorksheetFunction.CountA(Sheets("TOL Categories").Range(Range("B4", Target)))
CurrentList = 0

Also forgot to mention that "SectionInfant" is a defined range in my spreadsheet.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,820
Office Version
365
Platform
Windows
That line should be
Code:
CurrentList = Application.WorksheetFunction.CountA(Sheets("TOL Categories").Range("B4", Target))
But I think that what you need is
Code:
Dim CurrentList As Long
Dim Cl As Range
 
With Range("SectionInfant")
   Set Cl = .Offset(.Rows.Count - 2).Resize(1, 1)
End With
CurrentList = Application.WorksheetFunction.CountA(Sheets("TOL Categories").Range("B4", Cl))
Sheets("TOL Categories").Range("F1").Value = CurrentList
Also it's best to avoid using VBA keywords such as Target for variables.
 

Anthropologista

New Member
Joined
Nov 5, 2017
Messages
10
Fluff,
Thank you for your suggested code. It worked! I use VBA infrequently, so I very much appreciate the help provided by you and the other users.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,820
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Forum statistics

Threads
1,085,726
Messages
5,385,537
Members
401,958
Latest member
rsfalkowski

Some videos you may like

This Week's Hot Topics

Top