View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
L. Howard L. Howard is offline
external usenet poster
 
Posts: 852
Default 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