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

It just occurred to me that vArray(i - 1) is impossible since the first value is in A1 and there is no (A1 - 1).

I'll take a look at fixing that (and the spelling of my name)

Howard

Hi Howard,

Am Tue, 25 Nov 2014 11:40:52 -0800 (PST) schrieb L. Howard:

And with this line:
If vArray(i) = vArray(i - 1) Then

if you write a range into an array your array is a 2D Array
If vArray(i,1) = vArray(i - 1,1) Then:

Sub AnyDupesNum()
Dim i As Long, lr As Long, j As Long
Dim vArray As Variant

With Sheets("Sheet2")
lr = .Cells(Rows.Count, "A").End(xlUp).Row
vArray = .Range("A1:A" & lr)

For i = 2 To UBound(vArray)
If vArray(i, 1) = vArray(i - 1, 1) Then
j = j + 1
If j = 20 Then
.Cells(i, 2) = vArray(i, 1) & " = " & j
j = 0
End If
End If
Next 'i
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Spot on, Claus. Works very nice.

I have a long way to go with these arrays, for sure.

Thanks much.

Howard

Hi Howard,

Am Tue, 25 Nov 2014 12:21:34 -0800 (PST) schrieb L. Howard:

Works very nice.

no, there is a little error with the counter i
Better try:

Sub AnyDupesNum()
Dim i As Long, lr As Long, j As Long
Dim vArray As Variant

With Sheets("Sheet2")
lr = .Cells(Rows.Count, "A").End(xlUp).Row
vArray = .Range("A1:A" & lr)

For i = 2 To UBound(vArray)
If vArray(i, 1) = vArray(i - 1, 1) Then
j = j + 1
If j = 20 Then
.Cells(i - 1, 2) = vArray(i, 1) & " = " & j
j = 0
End If
Else
j = 0
End If
Next 'i
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I see, posts in the 21 row not the 20.

I missed that indeed.

Thanks,
Howard

Howard,

It helps to remember that data being 'dumped' into an array from a
worksheet is dimensioned same as the worksheet is, (n rows x n cols)
where 1st dim is the row count and 2nd dim is the col count. There is
no row0/col0 in Excel (or Calc) spreadsheets. Thus, the resulting array
will always be 1-based since the 1st row in a range is row1 and 1st col
is col1 regardless of the range's location on the sheet.

It also helps to think of both a worksheet and a 2D array as a x/y
grid.

You can also work 2D arrays via Index() same as you can a range, to
return a single row or col...

Dim vData, vTmp
vData = ActiveSheet.UsedRange
'n rows x n cols

'Get 1st row data only
vTmp = Application.Index(vData, 1, 0)
'1 row x n cols <equivalent of ReDim vTmp(1, n)

'Get 1st col data only
vTmp = Application.Index(vData, 0, 1)
'n rows x 1 col <equivalent of ReDim vTmp(n, 1)

...and you should get into the habit of typing loop counters as Long (&)
since sheet row counts exceed the limits of Integer (%) type.


A 1D array is a single row of n elements, regardless if it's 0-based or
1-based. This is why we need to Transpose() 1D arrays to put the
elements into a column.

What can get confusing for some is the 0/1 base. I'm not a fan of
mixing the 2 in loops so I'll often convert a 1-based array to 0-based
(or vice versa) if working a 0-based array along with it so the counter
(same for both) can be used LBound to UBound without +/- operators.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Revised to not show Smileys in place of text...

..and you should get into the habit of typing loop counters as Long
[&] since sheet row counts exceed the limits of Integer [%] type.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Tuesday, November 25, 2014 4:34:57 PM UTC-8, GS wrote:
Revised to not show Smileys in place of text...

..and you should get into the habit of typing loop counters as Long
[&] since sheet row counts exceed the limits of Integer [%] type.


Hi Garry,

Thanks for the continued education on arrays. Sometime I actually feel like I am clear up to about the third grade with arrays.<g

Regarding Dimming as Long. I always use Long now since the new row numbers in Excel are a million.

Is the Long with the counters any different than what I have in my first post?


As to using Index, I came across a discussion among MVP's using Index. Seemed to be something kinda new..?

I keep this example as a cheater guide, but don't understand the two 1's.
I typed =Index(... on the sheet and there are great similarities as you would expect, but the little syntax window couldn't 'splain it well enough to me.

- myRng, 1, 1,


Sub MyArryCellsRange()
Dim vArr As Variant
Dim myRng As Range

Set myRng = Range("M6, B28, A28, O1, E28, K3, C28, F28, D28, G28")

vArr = Application.Index(myRng, 1, 1, Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

Sheets("Sheet2").Range("I15").Resize(columnsize:=m yRng.Cells.Count) = vArr
Sheets("Sheet2").Range("H15").Resize(myRng.Cells.C ount, 1) = Application.Transpose(vArr)

End Sub

Howard