Populate an array without Iteration

[Joseph.Marro]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have searched everywhere and I am not sure if I can even accomplish this task. I know you can populate range values into an array without iteration. However, is it possible to convert a range into an array of row numbers without iteration? <o></o>
<o></o>
Example:

Sub Example()
Dim myRange As Range
Dim myArray() As Variant
Set myRange = Range("A23:A31")
myArray = myRange
Set myRange = Nothing
End Sub

<o></o>
How do I Modify this to produce an array containing the row numbers {23,24,25,26,27,28,29,30,31}? Is it even possible without iteration?<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro<o></o>
<o></o>

 

[GTO]

Mark

Wasn't sure who the blonde was so oops on my part too I suppose.

A fellow blonde?! No wonder I always enjoy your humor. "Flying squirrel guts" is still my favorite!

Only speaking for myself, but I think we need a support group. If I forget just a few more things, my poor noggin will implode from the vacuum.

Mark,

You didn't make an Oopsie, you were spot on. To answer your question, yes that is the ultimate goal.

Ahhh... Foggy as they may be, a few brain cells still exist! Quick, before they disappear, try this:

Option Explicit
    
Sub exa2()
Dim rng             As Range
Dim aryLookinVals   As Variant
Dim aryFoundAt      As Variant
Dim MyMatch         As Long
Dim x               As Long
Dim Col             As Long
Dim FirstRow        As Long
Dim ColMarker       As Long
    
    MyMatch = 29000
    
    Set rng = ThisWorkbook.Worksheets("Sheet2").Range("A2:A10001")
    aryLookinVals = rng.Value
    
    '// Initially size, bump the second dimension's base on first find  //
    ReDim aryFoundAt(1 To 2, 0 To 0)
    
    FirstRow = rng.Cells(1).Row
    ColMarker = rng.Cells(1).Column
    
    For x = 1 To UBound(aryLookinVals, 1)
        If MyMatch < aryLookinVals(x, 1) Then
            ReDim Preserve aryFoundAt(1 To 2, 1 To UBound(aryFoundAt, 2) + 1)
            Col = UBound(aryFoundAt, 2)
            '// store our row/col vals                                  //
            aryFoundAt(1, Col) = x + FirstRow - 1
            aryFoundAt(2, Col) = ColMarker
        End If
    Next
    
    '// In case we didn't have any matches, slip by the error           //
    Col = 0
    On Error Resume Next
    Col = UBound(aryFoundAt, 2)
    On Error GoTo 0
    
    If Col Then
        For x = 1 To UBound(aryFoundAt, 2)
            '// just to test, I marked the cells that passed the test//
            ThisWorkbook.Worksheets("Sheet2").Cells(aryFoundAt(1, x), aryFoundAt(2, x)).Interior.ColorIndex = 4
        Next
    End If
End Sub

Does that help?

Happy Friday all!

Mark

 

[GTO]

Ack! I forgot to mention, for my test, I just threw some random vals, from 1 to 30,000 in the range to see if I'd return the correct rows.

 

[xenou]

For row number ...
Just use an offset.

If first value of range is 100, then row number is the index of the array's row element + 99.

Ex:

[COLOR="Navy"]Sub[/COLOR] Foo()
[COLOR="Navy"]Dim[/COLOR] r [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] Long, j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a
[COLOR="Navy"]Dim[/COLOR] s
[COLOR="Navy"]Dim[/COLOR] wb [COLOR="Navy"]As[/COLOR] Workbook
[COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] firstRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

    [COLOR="SeaGreen"]'//Create Test Workbook[/COLOR]
    [COLOR="Navy"]Set[/COLOR] wb = Workbooks.Add
    [COLOR="Navy"]Set[/COLOR] ws = wb.Worksheets(1)
    
    [COLOR="SeaGreen"]'//Create Test Range[/COLOR]
    [COLOR="Navy"]Set[/COLOR] r = ws.Range("A10:B15")
    [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] r.Count
        r.Cells(i).Value = Chr(i + 64)
        r.Interior.ColorIndex = 6
    [COLOR="Navy"]Next[/COLOR] i
    
    [COLOR="SeaGreen"]'//Load array with values[/COLOR]
    a = r.Value
    firstRow = r.Cells(1).Row
    
    [COLOR="SeaGreen"]'//show array values and their row numbers[/COLOR]
    [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] UBound(a, 1)
        [COLOR="Navy"]For[/COLOR] j = 1 [COLOR="Navy"]To[/COLOR] UBound(a, 2)
            s = s & a(i, j) & " Row: " & (i + firstRow - 1) & vbNewLine
        [COLOR="Navy"]Next[/COLOR] j
    [COLOR="Navy"]Next[/COLOR] i
    MsgBox s

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Edit: This is basically the same as Mark's code. But I didn't actually bother writing any code to find matches ... point is, you just need to capture the row number of the first cell, and you can always derive any other row after that.

 

[RoryA]

For example:

Sub GetValuesAndRows()
    Dim rng As Range
    Dim varData
    Set rng = Range("A1:A10")
    varData = Evaluate("CHOOSE({1,2},ROW(" & rng.Address & ")," & rng.Address & ")")
End Sub

 

[Joseph.Marro]

Mark, Norie, Xenou, Rorya,

Thank you for all the assistance you have provided, it is greatly appreciated. I will apply the concepts mentioned over the weekend and let you know how it goes. Have a great weekend.

Thank you,

Joseph Marro

 

[Norie]

Joseph

I think Rory's got it - I made some pretty pathetic attempts of doing something with a formula but they really were pathetic.

 

[xenou]

Nice one, Rory.

 

[Joseph.Marro]

Rorya,

The code you provided is great. Sorry for not posting sooner. I thought I had replied. I guess I was too excited by the use of CHOOSE. I have found many uses for this method. Thank you agin for you help.

All,

Thank you for you help as well.

Thank you,

Joseph Marro