vArray = Sheet2.Range("A1:A" & lr) troubles ahoy
My public enemy the devil array().
Getting 'Subscript out of Range' error.
Column A is 1500+ values like 12.23 (unsure if from formulas or constants)
If the value repeats 20 times consecutively, then in column B at the 20th value return text i = 20 (the value = 20).
Am I even close?
And with this line:
If vArray(i) = vArray(i - 1) Then
can I use:
If i = (i - 1) Then
Thanks,
Hoard
Sub AnyDupesNum()
Dim i As Long, lr As Long, j As Long
Dim vArray As Variant
lr = Cells(Rows.Count, "A").End(xlUp).Row
vArray = Sheet2.Range("A1:A" & lr)
For i = LBound(vArray) To UBound(vArray)
j = 0
With (vArray(i))
If vArray(i) = vArray(i - 1) Then
j = j + 1
If j = 20 Then
vArray(i).Offset(, 1) = vArray(i) & " = " & j
j = 0
End If
End If
End With
Next 'i
End Sub
|