[L. Howard]

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

[L. Howard]

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

[Claus Busch]

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

[L. Howard]

Spot on, Claus. Works very nice.

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

Thanks much.

Howard

[Claus Busch]

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

[L. Howard]

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

I missed that indeed.

Thanks,
Howard

[GS[_2_]]

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

[GS[_2_]]

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

[L. Howard]

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

[GS[_2_]]

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.

Long is the default TYPE returned/expected when getting/setting the
index of a row/col. It's been my observation that many people assign
Type Integer to vars used for refs to row/col positions/counts or loop
counters.

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

Nope!

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,

There are 2 forms of the Index function: Array and Reference!

The assignment to 'vArr' in the *MyArryCellsRange()* example is
incorrect when using the 'Array' form of the Index function because it
only accepts 3 args: array(), row&, col&) as defined in the Function
Reference...

INDEX Uses an index to choose a value from a reference or array

Array form:
Returns the value of an element in a table or an array (array: Used to
build single formulas that produce multiple results or that operate on
a group of arguments that are arranged in rows and columns. An array
range shares a common formula; an array constant is a group of
constants used as an argument.), selected by the row and column number
indexes.

Use the array form if the first argument to INDEX is an array constant.

Syntax:
INDEX(array,row_num,column_num)

...where 'array' can be either a range of cells or an array constant.

Your sample code *does correctly example* using the 'Reference' form of
the Index function, though, because it assigns MyArray 'areas' (a
non-contiguous range)...

Reference form:
Returns the reference of the cell at the intersection of a particular
row and column. If the reference is made up of nonadjacent
selections,
you can pick the selection to look in.

Syntax:
INDEX(reference,row_num,column_num,area_num)

...where 'reference' is a ref to one or more cell ranges.

If you are entering a non-adjacent range for 'reference', enclose
'reference' in parentheses.

If each area in 'reference' contains only one row or column, the
row_num or column_num argument, respectively, is optional. For
example, for a single row reference, use
INDEX(reference,,column_num).

...and 'row_num' is the number of the row in 'reference' from which to
return a ref.

...and 'column_num' is the number of the column in 'reference' from
which to return a ref.

...and 'area_num' selects a range in reference from which to return the
intersection of row_num and column_num.

The first area selected or entered is numbered 1, the second is 2,
and so on. If 'area_num' is omitted, INDEX uses area 1.

For example, if 'reference' describes the cells (A1:B4,D1:E4,G1:H4),
then 'area_num' 1 is the range A1:B4, 'area_num' 2 is the range
D1:E4,
and 'area_num' 3 is the range G1:H4.

The above info is snipped from the help page. Read the entire help for
this function for further understanding. *Note* that this doesn't work
on 1D arrays!

--
Garry

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

[L. Howard]

Plenty to absorb there.

Thanks for taking the time.

Howard

[GS[_2_]]

Plenty to absorb there.

Thanks for taking the time.

Howard

You're welcome...

--
Garry

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