Count in the first free column

[Nelson78]

Hello everybody.

I've the following job.

https://www.dropbox.com/s/ek5nyx5lnfxclog/Develop.xlsm?dl=0

In the sheet "Beta" you can find the data source (column D).

In the sheet "Alpha", in the first free column, I've to count the quantity of recurrence.


Now, I've identified the first free column in Alpha with:

Sub Alphabet()


With Sheets("Alpha")

    For I = 1 To Columns.Count
        If Cells(4, I) = "" Then Exit For
    Next I

    freecolumn = I

End With


End Sub

Now, how can I create the results below in Alpha using vba?

B5 = 1
B6 = 7
B7 = 7
B8 = 3
B9 = 1
B10 = 19

 

[Scott Huish]

First, you don't need to loop to find the first free column. But wouldn't it always be column 2?
If there are going to be more columns of data, can you please show what you would want to do in that case?

 

[Scott Huish]

Assuming your results will always be in Column B:

Sub CountOccurrences()
Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("B5:B" & lastrow - 1).Formula = "=COUNTIF(Beta!D:D,A5)"
Range("B" & lastrow).Formula = "=SUM(B4:B" & lastrow - 1 & ")"
Range("B5:B" & lastrow).Value = Range("B5:B" & lastrow).Value
End Sub

 

[Nelson78]

First, you don't need to loop to find the first free column. But wouldn't it always be column 2?

No: suppose in B4 a header (example "20/2/18" produced by the sub); the following day the datas will be written in column C (the first free column with reference to row 4) and so on.

 

[Nelson78]

Assuming your results will always be in Column B:

Sub CountOccurrences()
Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("B5:B" & lastrow - 1).Formula = "=COUNTIF(Beta!D:D,A5)"
Range("B" & lastrow).Formula = "=SUM(B4:B" & lastrow - 1 & ")"
Range("B5:B" & lastrow).Value = Range("B5:B" & lastrow).Value
End Sub

As specified with the previous post, the sub always writes in the first free column (B, than C, than D, ...) so as to avoid datas overwriting.

 

[Scott Huish]

So, I understand, the totals would be overwritten the next day and then it would look something like this and then you want new totals??

Excel 2010
	A	B
4	LABELS	2/20/2018
5	LONDON	PARIS
6	PARIS	ATHENS
7	ROME
8	ATHENS
9	MADRID
10	TOTAL
Alpha