Vba excel countif

[kiwi101]

Hi guys,

My task is simple, but for some reason I'm confused on how to go about it so please direct me in the right way.

I want to count the number of time the number 4,3,2 and 1 appear in one of the columns in my table. I then want to create a new table listing these values, and create a chart in a new sheet. I've manually used the COUNTIF function to achieve this.
I now want to use VBA. I understand how to create and open a new sheet in VBA and also how to draw the chart of these values, however I am not finding much resources on how to create a new table and record the number of times 4,3,2 ,1 appear.
If possible, after creation of new sheet, I want it to populate with a table such as :

1 | 4
2 | 6
3 | 5
4 | 1

which means the number one appeared 5 times, the number 2 appeared 6 times etc etc. And I understand how to plot the graph on VBA.

Please help me out.

Curent Sub CreateSheet()<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;"> With ThisWorkbook
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Temp"
End With
End Sub</code>

 

[welshgasman]

I would record a macro where you have
Selected the temp sheet
Entered the values 1 to 4 in column A and the countif formula in column B
Stop recording.
Then open the macro and copy and paste the code to your module and tailor the code if need be.

HTH

 

[bhos123]

why do you need VBA, you can simply link the table to the Countif values and plot a grtaph..

 

[kiwi101]

I would record a macro where you have
Selected the temp sheet
Entered the values 1 to 4 in column A and the countif formula in column B
Stop recording.
Then open the macro and copy and paste the code to your module and tailor the code if need be.

HTH

This worked like a charm! I really appreciate it. I feel like I'm up for a good start.
I have 2 more questions:

a) For the associated chart I want to make, where should I add this code?
I am basically adding a button on my sheet 1 with data called create chart and I have assigned this macro to it. So when you click CreateChart a new sheet opens up with these data values. Now should I record the macro while making the chart or is there a way I can add this pre exsiting code somewhere? What would you recommend?

Sub CreateChart()
'PURPOSE: Create a chart (chart dimensions are not required)




Dim rng As Range
Dim cht As Object




'Your data range for the chart
Set rng = ActiveSheet.Range("A1:D6")




'Create a chart
Set cht = ActiveSheet.Shapes.AddChart2




'Give chart some data
cht.Chart.SetSourceData Source:=rng




'Determine the chart type
cht.Chart.ChartType = xlColumnStacked100

cht.Chart.SetElement (msoElementDataLabelCenter)

cht.Chart.SetElement (msoElementLegendTop)












End Sub



b) I basically have 3 columns A,B and C. Column B can either have the value KK or LL. I have already sorted the columns so first all the LL's appear and then all the KK's. How do I let the macro itself stop at the last LL without having to manually assign the value of the cell number?

 

[welshgasman]

I would again record the steps you take to create the chart. Try and take your time, so you do not make mistakes, less editing to do then.
I'd also give some thought to post #3 if it is less work.