Excel - Dynamic COUNTIF Forrmula

I am not an expert VBA writer but can typically adapt codes to effectively provide the output I desire. However after two days I am admitting defeat.

My plans was to adapt this coding I found to:
For i = 1 To 10000
If Len(Range("J" & i)) = 0 Then
If Len(Range("I" & i)) > 0 Then
Range("J" & i).Formula = "=INT(RAND()*100)"
End If
End If
Next i

This is the formula I need to insert in the new blank column created next to column "I":
'=COUNTIF($I$2:$I$1941,I2)

But I have been unsuccessful because the range changes each time as the list will begin longer or shorter. Please help and I greatly appreciate your help.


Below is my entire VBA information if it helps.
Sub Delete_with_Autofilter_Two_Criteria()
Dim DeleteValue1 As String
Dim DeleteValue2 As String
Dim rng As Range
Dim calcmode As Long
Dim i As Integer

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Rows("1:2").Select
Selection.Delete Shift:=xlUp

'Fill in the two values that you want to delete
DeleteValue1 = "NULL"
DeleteValue2 = " "

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("I1:I" & .Rows.Count).AutoFilter Field:=1, _
Criteria1:=DeleteValue1, Operator:=xlOr, Criteria2:=DeleteValue2

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

With Application
Range("J:J").Insert Shift:=xlToRight
Range("J1").Select
ActiveCell.FormulaR1C1 = "Counter"
End With

'=COUNTIF($I$2:$I$1941,I2)

For i = 1 To 10000
If Len(Range("J" & i)) = 0 Then
If Len(Range("I" & i)) > 0 Then
Range("J" & i).Formula = "=INT(RAND()*100)"
End If
End If
Next i

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories