COUNTIF Column based in VBA

[BGerm]

I am trying to count number of 1 in every 5th column and write it in 7th line of that column. I got error Object required. I am beginner so probably I missed something important.


Sub Countif()

Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws1 = wb.Sheets("Sheet2")

Dim ValuesRange As Range
Dim ResultCell As Range
Dim CriteriaValue As String



LastRow = ws1.Cells(ws1.Rows.Count, "J").End(xlUp).Row
LastColumn = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column

For j = 10 To LastColumn Step 5
For i = 2 To LastRow

Set ValuesRange = Range("j8:j2197")
Set ResultCell = cellRange.Cells(7, j)

CriteriaValue = 1

ResultCell = WorksheetFunction.Countif(ValuesRange, CriteriaValue)

Next i

Next j

End Sub

[Paul_Hossler]

Probably 2 issues with




Set ValuesRange = Range("j8:j2197")
Set ResultCell = cellRange.Cells(7, j)



1. Valuesrange will ALWAYS be col J
2. cellRange is not Set (the error message)

You probably wanted something like




Set ValuesRange = Range(cells(8,j), cells(LastRow, j))
Set ResultCell = Range.Cells(7, j)