reorder array by index?

[sous2817]

Hello everyone,

Currently I'm building up an array by looping through a set of columns with code like:

For i = 1 To lSampleColumn
    ReDim Preserve vStr(1 To i)
    aStr = SampleDataSheet.Cells(1, i).Value & ": " & SampleDataSheet.Cells(SampleLoop, i).Value
    vStr(i) = aStr
Next i
Set shp = ws.Shapes(shapename)
ws.Hyperlinks.Add shp, "", "", ScreenTip:=Join(vStr, vbCr)

That all works fine, but what I'm hoping to do is reorder the array index from {1,2,3,4,etc, etc, etc} to something like: {1,x,2,3,etc,etc,etc} or {1,x,2,4,etc,etc,etc}

The x is defined earlier in the code, it's an integer that defines what column they want to apply some color to.

Index 1 is the description (in this case country name) and will always be first. The remaining parts of the array are various values that the user assigns to the country. I started with the assumption that they'd always put the "use this value to apply colors to" in column B, but that's not always going to be the case. So my thinking was, if possible, it'd be easier to reorder the array on the back end.

I'm aware that another solution would be to move the columns on the entry sheet, which I can go down that road if necessary, but seems like it'd be easier and less overhead to just reorder the array index.

Hope that makes sense, and any help is greatly appreciated.

Thanks,
sous

 

[ChrisM]

How about using a second array as the translator? It's a one dimensional array:

Dim myArray(1 to 10) as Long

The size of the array, 1 to 10, corresponds to the total number of columns you could read into your original array.

The value stored within this new array is the "real" column that the user wanted.

So using your example:

{1,x,2,3,etc,etc,etc}

myArray(1) = 1
myArray(2) = "x"
myArray(3) = 2
etc

Now when you go to write your array to the worksheet, you actually loop through the new array first:

For i = 1 to 10
   worksheetrange = vstr(myArray(i))
next i

Does that make sense?

 

[sous2817]

Thanks for the input ChrisM,

This gets me close enough...I used a "helper" array as you suggested. The first array is populated w/ some test data, more interested in the 2nd array:

Sub junk2()
Dim i As Long, j As Long
Dim vStr(), nStr(1 To 10)

j = 6

For i = 1 To 10
ReDim Preserve vStr(1 To i)
    vStr(i) = i
Next i

nStr(1) = vStr(1)
nStr(2) = vStr(j)
For i = 3 To 10
        If i <> j Then
            nStr(i) = vStr(i)
        Else:
            nStr(j) = vStr(2)
        End If
Next i
End Sub