array to range

Corticus

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
Wassup fellow Excelers,

I would like to take a single dimension array and display the values down a range of cells. Can I do this?

In this example, I create a 6 element array from the range("A1:A6").

Then I do some array manipulation (like quicksort).

Now I want to return the array to a range of cells.

Kind of like this, but without the error:
Book2
ABCD
1F#NAME?
2E#NAME?
3D#NAME?
4C#NAME?
5B#NAME?
6A#NAME?
Sheet1


In this case, I would want to use the formula just like I did, but [B1] would display the array(1), [B2] would display array(2) etc.

Thanks in advance for any help!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Yep, you need a 2-d array, and proper sizing.

How do you stack this, it should be a 2-d if you simply grab it with a quick stack via variant array. Did you transpose? If so, you'll want to transpose it again to get back to a 2-d.

So, if it's Excel < XP, and array element size < 5461 -> [MyRange] = Application.Transpose(MyArray)
 
Upvote 0
If Sorter is capable of returning an array, you could use...

For example: from E1 on...

=INDEX(Sorter(A$1:A$6),ROW()-ROW($E$1)+1)
 
Upvote 0
Thanks NateO,

I get the 2d array from the range, make it 1d (probably the wrong way), run quicksort on it, and then I want it back to a range (I guess it needs to be 2d)

Here's what I've got, but this is just an learning example:
Code: 
Function sorter(rng As Range) As Variant

    Dim arr2() As Variant
    Dim arr1() As Variant
    Dim i As Integer
    Dim ct As Integer

    ct = rng.Count

    arr2() = rng

    ReDim arr1(1 To ct)

    For i = 1 To ct
        arr1(i) = arr2(i, 1)
    Next i

    Call QuickSort(arr1)

End Function

Public Sub QuickSort(ByRef vntArr As Variant, _
    Optional ByVal lngLeft As Long = -2, _
    Optional ByVal lngRight As Long = -2)

    Dim i, j, lngMid As Long
    Dim vntTestVal As Variant

    If lngLeft = -2 Then lngLeft = LBound(vntArr)
    If lngRight = -2 Then lngRight = UBound(vntArr)

    If lngLeft < lngRight Then
        lngMid = (lngLeft + lngRight) \ 2
        vntTestVal = vntArr(lngMid)
        i = lngLeft
        j = lngRight
        Do
            Do While vntArr(i) < vntTestVal
                i = i + 1
            Loop
            Do While vntArr(j) > vntTestVal
                j = j - 1
            Loop
            If i <= j Then
                Call SwapElements(vntArr, i, j)
                i = i + 1
                j = j - 1
            End If
        Loop Until i > j

        If j <= lngMid Then
            Call QuickSort(vntArr, lngLeft, j)
            Call QuickSort(vntArr, i, lngRight)
        Else
            Call QuickSort(vntArr, i, lngRight)
            Call QuickSort(vntArr, lngLeft, j)
        End If
    End If
    
End Sub

Private Sub SwapElements(ByRef vntItems As Variant, _
    ByVal lngItem1 As Long, _
    ByVal lngItem2 As Long)

    Dim vntTemp As Variant

    vntTemp = vntItems(lngItem2)
    vntItems(lngItem2) = vntItems(lngItem1)
    vntItems(lngItem1) = vntTemp

End Sub

Everything works, I just don't know how to get the array back on the sheet, besides adding an argument to the function whcih specifies which element in the array I want.

Thanks for your sapient advice! :lol:

edit: Thanks Aladin, as well, just got your post!

-Corticus
 
Upvote 0
Corticus said:
Thanks NateO,

Thanks for your sapient advice! :lol:
Click to expand...
:lol:

You're welcome. I tweaked your code a bit and came up with the following:<font face=Courier New><SPAN style="color:darkblue">Function</SPAN> StrSort(myStr<SPAN style="color:darkblue">As</SPAN> Range, y<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Integer</SPAN>,<SPAN style="color:darkblue">Optional</SPAN> Descend<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Boolean</SPAN>)<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">String</SPAN><SPAN style="color:darkblue">Dim</SPAN> b()<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Byte</SPAN>, i<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Integer</SPAN>, j<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Integer</SPAN>, LwVl<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Integer</SPAN>
b = StrConv(Join(Application.Transpose(myStr), vbNullString), vbFromUnicode)<SPAN style="color:darkblue">For</SPAN> i =<SPAN style="color:darkblue">LBound</SPAN>(b)<SPAN style="color:darkblue">To</SPAN><SPAN style="color:darkblue">UBound</SPAN>(b)
    LwVl = b(i)
    <SPAN style="color:darkblue">For</SPAN> j = i + 1<SPAN style="color:darkblue">To</SPAN><SPAN style="color:darkblue">UBound</SPAN>(b)
        <SPAN style="color:darkblue">If</SPAN> Choose(-CInt(Descend) + 1, _
            b(j)< LwVl, b(j) > LwVl)<SPAN style="color:darkblue">Then</SPAN>
              LwVl = b(j)
              b(j) = b(i)
              b(i) = LwVl
        <SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">If</SPAN>
    <SPAN style="color:darkblue">Next</SPAN><SPAN style="color:darkblue">Next</SPAN>
StrSort$ = ChrW$(b(y - 1))
Erase b<SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">Function</SPAN></FONT>

Which lead me to the following:
Book2
ABCD
1InputDescendingAscending
2EFA
3BEB
4FDC
5CCD
6DBE
7AAF
Sheet2


What d'ya think? :)
 
Upvote 0
:lol: And why not return the entire array as the function and use INDEX as Aladin suggested ? In fact, I would just array enter the function to get the desired results.... not just return an element one by one !
 
Upvote 0
Aladin Akyurek said:
Nate: Insert a row before the data. :evil:
Click to expand...
??? I don't follow. Yes, I did, just to show some titles, delete the titles, change the input range and change row()-1 to row(), relatively straight-forward.

In any case, Corticus, I didn't follow what you meant earlier. But yes, use transpose, you must use an element index as some point, work with a byte array, it's faster than a variant or string array.

Hope this helps. :-D

Juan, why return the entire array if you simply want an element?
 
Upvote 0
This is what I'm talking about, using the same formula from Cort:<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> sorter(rng<SPAN style="color:#00007F">As</SPAN> Range)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> arr1()<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> i<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> ct<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">If</SPAN> rng.Columns.Count > 1<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Function</SPAN>
    
    ct = rng.Count
    <SPAN style="color:#00007F">ReDim</SPAN> arr1(1<SPAN style="color:#00007F">To</SPAN> rng.Rows.Count)
    <SPAN style="color:#00007F">For</SPAN> i = 1<SPAN style="color:#00007F">To</SPAN> rng.Rows.Count
        arr1(i) = rng(i)
    <SPAN style="color:#00007F">Next</SPAN> i
    QuickSort arr1
    sorter = arr1<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>
Book2
ABCD
2EA
3BB
4FC
5CD
6DE
7AF
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Applying date ordinal to an array of dates.
Replies
2
Views
167
HighAndWilder
H
J
  • Question Question
Group last name based on range in alphabet
Replies
2
Views
270
Peter_SSs
Peter_SSs
L
  • Solved
Formatting cells filled by array formulae
Replies
5
Views
287
Ludwig
L
C
  • Solved
Can I search multiple columns for cells that contain a specific number plus other random text?
Replies
3
Views
74
Fluff
Fluff
E
  • Question Question
Function to return array of 10 largest absolute values while maintaining sign
Replies
3
Views
588
eurobonds
E

Forum statistics

Threads
1,225,322
Messages
6,184,279
Members
453,227
Latest member
Slainte

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top