Numbered column in vba

[PcMax]

Hi,

I messed around a bit 'to run in a loop vba.
I wonder if you can better manage this cycle with an array

Option Explicit
Public Const RigaUp = 2

Sub a()
    Dim RigaDn As Long
    Dim Giorni As Variant
    Dim ciclo As Long
    Dim ciclo1 As Long
    Dim start As Integer
    Dim Old As Integer
    RigaDn = Cells(Rows.Count, 1).End(xlUp).Row
    Giorni = Range(Cells(RigaUp, 4), Cells(RigaDn, 5)).Value
    For ciclo = 1 To UBound(Giorni)
        Giorni(ciclo, 1) = Int(Giorni(ciclo, 1))
    Next
    Range("AA2:AA21") = Giorni
    For ciclo1 = 1 To UBound(Giorni)
        Cells(ciclo1 + 1, 28) = Contig(Range("AA" & ciclo1 + 1 & ":AA22"), Int(Giorni(ciclo1, 1)))
    Next
End Sub

Function Contig(ByRef Myaddr As Range, ByVal myVal As String) As Integer
For Each cell In Myaddr
    If cell.Value = myVal Then
        ccnt = ccnt + 1
    Else
        If ccnt > Contig Then Contig = ccnt
        ccnt = 0
    End If
Next cell
End Function

[Rick Rothstein]

I messed around a bit 'to run in a loop vba.

If you really need to do this in VBA (instead of using one of the formulas Admin or I posted earlier), then you can use this macro...

Sub CountDupesBackwards()
  Dim LastRow As Long
  Const DataColumn As String = "A"
  Const OutputColumn As String = "B"
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  With Cells(StartRow, OutputColumn).Resize(LastRow - StartRow + 1)
    .Formula = "=COUNTIF(" & DataColumn & StartRow & ":" & DataColumn & _
               "$" & LastRow & "," & DataColumn & StartRow & ")"
    .Value = .Value
  End With
End Sub