Hi Don,
Trust me, there's enough code I don't understand either, but getting good enough to craft something together to get me by.
This x is a variable which is initially set to -1. I basically means, from the active cell, -1.
Therefore, as we place the first Countif in column B, if you -1, the Countif is pointing to column A.
The .Value = .Value is basically taking all values in B2:B21 and making them values.
Code:
Sub ApplyCountif()
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long
Dim RPosition As Long
Dim rngData As Range
LastCol = Cells(1, 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
RPosition = InStr(1, Cells(1, i), 1)
Application.ScreenUpdating = False
With Cells(2, i + 1).Resize(LastRow)
.Formula = sFormula
.Value = .Value
If RPosition <> 0 Then
Set rngData = Cells(2, i + 1).Resize(LastRow)
rngData = Evaluate(rngData.Address & "*2")
.NumberFormat = "0;;;"
End If
End With
Next i
Application.ScreenUpdating = True
End Sub
No doubt somebody else could've or would've coded this requirement different, but if it achieve the desired result, the only thing left is speed.
To get a better picture of what the macro is doing, Step through the code using F8. You might also want to read up on debugging code or setting watches.
http://www.mrexcel.com/forum/lounge-...cks-gurus.html
http://www.excelforum.com/the-water-...hers-wont.html
http://www.mrexcel.com/forum/general...lications.html