Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vArray = Sheet2.Range("A1:A" & lr) troubles ahoy
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vArray = Sheet2.Range("A1:A" & lr) troubles ahoy
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vArray = Sheet2.Range("A1:A" & lr) troubles ahoy
Spot on, Claus. Works very nice.
I have a long way to go with these arrays, for sure. Thanks much. Howard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vArray = Sheet2.Range("A1:A" & lr) troubles ahoy
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vArray = Sheet2.Range("A1:A" & lr) troubles ahoy
I see, posts in the 21 row not the 20.
I missed that indeed. Thanks, Howard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
vArray = Sheet2.Range("A1:A" & lr) troubles ahoy
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
vArray = Sheet2.Range("A1:A" & lr) troubles ahoy
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
vArray = Sheet2.Range("A1:A" & lr) troubles ahoy
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
vArray = Sheet2.Range("A1:A" & lr) troubles ahoy
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
vArray = Sheet2.Range("A1:A" & lr) troubles ahoy
Plenty to absorb there. Thanks for taking the time. Howard |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
vArray = Sheet2.Range("A1:A" & lr) troubles ahoy
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if any range on sheet2 change put "a" in sheet1 cell a111 | Excel Programming | |||
Converting a text form of "=Sheet2!A1" into a reference formula | Excel Discussion (Misc queries) | |||
how do copy "sheet1!A1+1 in sheet2 to sheet 3 and get "sheet2!A1+ | Excel Discussion (Misc queries) | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming |