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
I have the following code so far:
Sub ChartCreation()
Cells.Select
Selection.Sort Key1:=Range("A2"
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"
Range("G1"
Selection.EntireColumn.Hidden = True
Cells.Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("ChartInfo"
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub