Creating Charts in Excel VBA and using CountIF 1

[hahnsm]

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&quot, 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&quot.Select
Range("G1&quot.Activate
Selection.EntireColumn.Hidden = True
Cells.Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("ChartInfo&quot.Range("A:H,X:X&quot, _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

 

[Chattin]

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 ;-)

 

[SkipVought]

Hi,

2 parts here.

1. use the SUBTOTAL worksheet function which only works on VISIBLE cells

=SUBTOTAL(2,YourRange)

2. Use the AutoFilter or Advanced Filter to select only rows meeting your requirement.

VOLA!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[hahnsm]

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!

 

[SkipVought]

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

http://www.TheOfficeExperts.com

 

[hahnsm]

Great thanks Skip!!

Sadie