Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating Charts in Excel VBA and using CountIF 1

Status
Not open for further replies.

hahnsm

MIS
Jun 17, 2003
62
US
I am having problem with some of my VBA code. I need charts to set up based on category. I want the subtotals to not be by sum but by Count. I want the subcount to count all of the 1's given in each column per category and to only count just the 1's. How can I do this?? How can I use a CountIF in my code?? I am teaching myself VBA so I may not have my code set up correctly and could use some help. Also with this code, I am not getting the summary below the data.

I have the following code so far:

Sub ChartCreation()
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Subtotal GroupBy:=8, Function:=xlCount, TotalList:=Array(25), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Columns("C:G").Select
Range("G1").Activate
Selection.EntireColumn.Hidden = True
Cells.Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("ChartInfo").Range("A:H,X:X"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub


 
Countif is one of the worksheet functions abailable to VBA - search the VBA help on 'worksheet function' without quote. The general syntax is

Application.WorksheetFunction.functionname(parameters)

HTH

Chris ;-)
 
Hi,

2 parts here.

1. use the SUBTOTAL worksheet function which only works on VISIBLE cells
Code:
=SUBTOTAL(2,YourRange)
2. Use the AutoFilter or Advanced Filter to select only rows meeting your requirement.

VOLA! :)

Skip,
Skip@TheOfficeExperts.com
 
Thanks to both of you for responding!
I do have a couple of questions:

Chris: Where can I use the CountIf function in my code shown above??

Skip: The subtotal you mention, is this to help me with the summary of counts?? I am confused on how to use the Advanced filter. Can you help me further or direct me somewhere else??

Thanks!
 
You need to read the Excel Help on the SUBTOTAL function to understand ALL that it can do.

Advanced filter is generally used for more complex criteria, since AutoFilter will accomodate only 2 criteria.

In your case, I would probably use the AutoFilter and select counts of 1, using the SUBTOTAL function to count the visible cells in that column. :)

Skip,
Skip@TheOfficeExperts.com
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top