Numbered column in vba

**PcMax** · 07-01-2012, 12:30 PM

**Admin** · 07-01-2012, 12:59 PM

**PcMax** · 07-01-2012, 09:03 PM

Hi,

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

Code:

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

Code:

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** · 07-01-2012, 11:54 PM

Originally Posted by Admin

Hi

You could try this formula.

=COUNTIF($A$1:$A$9,A1)-COUNTIF($A$1:A1,A1)+1

Another formula that can be used is this one...

=COUNTIF(A1:A$9,A1)

Change both A1 references to the cell reference for the cell that your data actually starts at.

**Rick Rothstein** · 07-02-2012, 12:16 AM

Originally Posted by PcMax

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...

Code:

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

**snb** · 07-02-2012, 12:57 AM

You could use:

Code:

Sub snb()
  sn = Columns(1).SpecialCells(2)
    
  For j = 1 To UBound(sn)
    sn(j, 1) = UBound(Filter(Application.Transpose(sn), sn(j, 1))) + 1
  Next
    
  Columns(1).SpecialCells(2).Offset(, 3) = sn
End Sub

**PcMax** · 07-02-2012, 01:58 AM

Hi,

I have tried and tested various suggestions recommended.
Code Working successfully Thank Snb

**Rick Rothstein** · 07-02-2012, 02:20 AM

Originally Posted by PcMax

I have tried and tested various suggestions recommended.
Code Working successfully Thank Snb

One caution with snb's code... it will work fine as long as all the values in your list are the same length (as you showed in your posting). However, if the length of the list items can vary, and more importantly, if a smaller value can be found as part of a larger value (for example, if your list could have both 1850 and 41850 in it), then snb's code will return incorrect answers.

**snb** · 07-02-2012, 02:33 AM

**PcMax** · 07-02-2012, 02:40 AM

Hi,

My research uses the data in a column that I find in columns and refer to a date for equity securities.
If all the data page are ordered and am in uniform, I should not have the problem reported.
If the dates were not in order, however, would get positioning errors of the formulas.
I could use an initial course to test the correctness of the data before running the code.