Loop through a column

jeffreybrown · #1 09-22-2016, 07:21 PM

Yes, this is a macro which will apply the Countif formula to each of the four columns (B, D, F, and H). You initially descried what you want as 10 columns with an unknown row number, but you sample is 4 columns with the same number of rows. I realize it is a sample, but the reason I mention it, the code could be written slightly different for all different types of scenarios.

In the end, if you do not want a macro, the formula by itself will suffice. This macro creates the Countif formula, but then copies it as values instead of leaving the formula intact.

If you want to just use a formula, paste into B2 and copy down...

=IF(A1<>A2,COUNTIF(A$2:A$14,A2),"")

...now apply to the other columns, but again, this is only if the row numbers are the same. If they are not, just adjust A$14 to the length of your rows.

Code:

Sub ApplyCountif()
    Dim LastRow As Long
    Dim LastCol As Long
    Dim i As Long
    
    LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    
    Const x As Long = -1
    Const sFormula As String = "=IF(R[" & x & "]C[" & x & "]<>RC[" & x & "],COUNTIF(C[" & x & "],RC[" & x & "]),"""")"
    
    For i = 1 To LastCol Step 2

        LastRow = Cells(Rows.Count, i).End(xlUp).Row - 1

        Application.ScreenUpdating = False

        With Cells(2, i + 1).Resize(LastRow)
            .Formula = sFormula
            .Value = .Value
        End With

    Next i

    Application.ScreenUpdating = True

End Sub

Paste code in a Normal module

Where to paste code
Highlight macro to copy >> Ctrl + C >> Open your workbook
Alt + F11 >> opens the Visual Basic Editor (VBE)
Ctrl + R >> opens the Project Explorer (if not already open on left side of screen)
Insert menu >> Module or Alt + I, M >> activates the Insert menu and inserts a Standard Module
Paste code >> Ctrl + V (right side of screen)
Alt + Q >> exits VBE and returns to Excel
Back in Excel >> Alt + F8 >> Macro Dialog Box >> Highlight macro >> Run

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Timeline - loop through all dates between first and last given and add date to column if not found	carlos_cs	Excel Programming	1	05-03-2016 09:25 AM
documents saved with double column revert to single column when re-opened	danw	Word	6	04-08-2015 06:24 AM
Embedded Excel in Powerpoint - custom animation, display column by column	andytheanimal	PowerPoint	2	01-20-2015 06:30 AM
How can I temporarily break a 3 column format in order to type a single column paragraph	William P	Word	1	01-04-2015 06:40 PM
Want a quotient using a constant to show up in column c each time a number is added to column b	fibbermcghee	Excel	2	12-09-2014 05:48 PM