BGerm
02-17-2020, 04:18 AM
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
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