Array question
 

I am having a problem with arrays I am trying to add to an array with 3
columns, I had to use the code below but how can I sort it by the first
column or is there another way of populating the array
Regards
Charles

ElimentCount = ElimentCount + 1
ReDim Preserve myArray(1 To 3, 1 To ElimentCount)

myArray(1, ElimentCount) = vqSize
myArray(2, ElimentCount) = vqRoot
myArray(3, ElimentCount) = fName

Array question
 

if you want to use a dynamic array and redim preserve, then you will have to
use the approach you show.

Here is a bubble sort to sort on the first row ("column" in your transposed
array)
Function SortArray(myArray)
Dim temp, i, j, i
For i = LBound(myArray, 2) To UBound(myArray, 2) - 1
For j = i + 1 To UBound(myArray, 2)
If myArray(1, i) myArray(1, j) Then
For k = LBound(myArray, 1) To UBound(myArray, 1)
temp = myArray(k, i)
myArray(k, i) = myArray(k, j)
myArray(k, j) = temp
Next
End If
Next
Next
SortArray = myArray
End Function

Here is a test function used to test it.
Put random data in A1:F2, then run the Tester macro

Sub Tester()
Dim myArray
myArray = Range("A1:F3").Value
myArray = SortArray(myArray)
Range("A1:F3") = myArray
End Sub

--
Regards,
Tom Ogilvy

"vqthomf" wrote:

I am having a problem with arrays I am trying to add to an array with 3
columns, I had to use the code below but how can I sort it by the first
column or is there another way of populating the array
Regards
Charles

ElimentCount = ElimentCount + 1
ReDim Preserve myArray(1 To 3, 1 To ElimentCount)

myArray(1, ElimentCount) = vqSize
myArray(2, ElimentCount) = vqRoot
myArray(3, ElimentCount) = fName

Array question
 

Thanks is it possible to do it in desc order?
regards
Charles

"Tom Ogilvy" wrote:

if you want to use a dynamic array and redim preserve, then you will have to
use the approach you show.

Here is a bubble sort to sort on the first row ("column" in your transposed
array)
Function SortArray(myArray)
Dim temp, i, j, i
For i = LBound(myArray, 2) To UBound(myArray, 2) - 1
For j = i + 1 To UBound(myArray, 2)
If myArray(1, i) myArray(1, j) Then
For k = LBound(myArray, 1) To UBound(myArray, 1)
temp = myArray(k, i)
myArray(k, i) = myArray(k, j)
myArray(k, j) = temp
Next
End If
Next
Next
SortArray = myArray
End Function

Here is a test function used to test it.
Put random data in A1:F2, then run the Tester macro

Sub Tester()
Dim myArray
myArray = Range("A1:F3").Value
myArray = SortArray(myArray)
Range("A1:F3") = myArray
End Sub

--
Regards,
Tom Ogilvy

"vqthomf" wrote:

I am having a problem with arrays I am trying to add to an array with 3
columns, I had to use the code below but how can I sort it by the first
column or is there another way of populating the array
Regards
Charles

ElimentCount = ElimentCount + 1
ReDim Preserve myArray(1 To 3, 1 To ElimentCount)

myArray(1, ElimentCount) = vqSize
myArray(2, ElimentCount) = vqRoot
myArray(3, ElimentCount) = fName

Array question
 

Just change the to < in the 5th line of the function.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vqthomf" wrote in message
...
Thanks is it possible to do it in desc order?
regards
Charles

"Tom Ogilvy" wrote:

if you want to use a dynamic array and redim preserve, then you will
have to
use the approach you show.

Here is a bubble sort to sort on the first row ("column" in your
transposed
array)
Function SortArray(myArray)
Dim temp, i, j, i
For i = LBound(myArray, 2) To UBound(myArray, 2) - 1
For j = i + 1 To UBound(myArray, 2)
If myArray(1, i) myArray(1, j) Then
For k = LBound(myArray, 1) To UBound(myArray, 1)
temp = myArray(k, i)
myArray(k, i) = myArray(k, j)
myArray(k, j) = temp
Next
End If
Next
Next
SortArray = myArray
End Function

Here is a test function used to test it.
Put random data in A1:F2, then run the Tester macro

Sub Tester()
Dim myArray
myArray = Range("A1:F3").Value
myArray = SortArray(myArray)
Range("A1:F3") = myArray
End Sub

--
Regards,
Tom Ogilvy

"vqthomf" wrote:

I am having a problem with arrays I am trying to add to an array with
3
columns, I had to use the code below but how can I sort it by the
first
column or is there another way of populating the array
Regards
Charles

ElimentCount = ElimentCount + 1
ReDim Preserve myArray(1 To 3, 1 To ElimentCount)

myArray(1, ElimentCount) = vqSize
myArray(2, ElimentCount) = vqRoot
myArray(3, ElimentCount) = fName

Array question
 

Change the to< to what?
Regards
Charles

"Bob Phillips" wrote:

Just change the to < in the 5th line of the function.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vqthomf" wrote in message
...
Thanks is it possible to do it in desc order?
regards
Charles

"Tom Ogilvy" wrote:

if you want to use a dynamic array and redim preserve, then you will
have to
use the approach you show.

Here is a bubble sort to sort on the first row ("column" in your
transposed
array)
Function SortArray(myArray)
Dim temp, i, j, i
For i = LBound(myArray, 2) To UBound(myArray, 2) - 1
For j = i + 1 To UBound(myArray, 2)
If myArray(1, i) myArray(1, j) Then
For k = LBound(myArray, 1) To UBound(myArray, 1)
temp = myArray(k, i)
myArray(k, i) = myArray(k, j)
myArray(k, j) = temp
Next
End If
Next
Next
SortArray = myArray
End Function

Here is a test function used to test it.
Put random data in A1:F2, then run the Tester macro

Sub Tester()
Dim myArray
myArray = Range("A1:F3").Value
myArray = SortArray(myArray)
Range("A1:F3") = myArray
End Sub

--
Regards,
Tom Ogilvy

"vqthomf" wrote:

I am having a problem with arrays I am trying to add to an array with
3
columns, I had to use the code below but how can I sort it by the
first
column or is there another way of populating the array
Regards
Charles

ElimentCount = ElimentCount + 1
ReDim Preserve myArray(1 To 3, 1 To ElimentCount)

myArray(1, ElimentCount) = vqSize
myArray(2, ElimentCount) = vqRoot
myArray(3, ElimentCount) = fName

Array question
 

Here is a bubble sort to sort on the first row ("column" in your transposed
array)
Function SortArray(myArray)
Dim temp, i, j, i
For i = LBound(myArray, 2) To UBound(myArray, 2) - 1
For j = i + 1 To UBound(myArray, 2)
If myArray(1, i) myArray(1, j) Then '<<<<<<<<<<<<<<<<<<<<< HERE
For k = LBound(myArray, 1) To UBound(myArray, 1)
temp = myArray(k, i)
myArray(k, i) = myArray(k, j)
myArray(k, j) = temp
Next
End If
Next
Next
SortArray = myArray
End Function

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vqthomf" wrote in message
...
Change the to< to what?
Regards
Charles

"Bob Phillips" wrote:

Just change the to < in the 5th line of the function.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vqthomf" wrote in message
...
Thanks is it possible to do it in desc order?
regards
Charles

"Tom Ogilvy" wrote:

if you want to use a dynamic array and redim preserve, then you will
have to
use the approach you show.

Here is a bubble sort to sort on the first row ("column" in your
transposed
array)
Function SortArray(myArray)
Dim temp, i, j, i
For i = LBound(myArray, 2) To UBound(myArray, 2) - 1
For j = i + 1 To UBound(myArray, 2)
If myArray(1, i) myArray(1, j) Then
For k = LBound(myArray, 1) To UBound(myArray, 1)
temp = myArray(k, i)
myArray(k, i) = myArray(k, j)
myArray(k, j) = temp
Next
End If
Next
Next
SortArray = myArray
End Function

Here is a test function used to test it.
Put random data in A1:F2, then run the Tester macro

Sub Tester()
Dim myArray
myArray = Range("A1:F3").Value
myArray = SortArray(myArray)
Range("A1:F3") = myArray
End Sub

--
Regards,
Tom Ogilvy

"vqthomf" wrote:

I am having a problem with arrays I am trying to add to an array
with
3
columns, I had to use the code below but how can I sort it by the
first
column or is there another way of populating the array
Regards
Charles

ElimentCount = ElimentCount + 1
ReDim Preserve myArray(1 To 3, 1 To ElimentCount)

myArray(1, ElimentCount) = vqSize
myArray(2, ElimentCount) = vqRoot
myArray(3, ElimentCount) = fName

Array question
 

Thanks for the help.
Regards
Charles

"Bob Phillips" wrote:

Here is a bubble sort to sort on the first row ("column" in your transposed
array)
Function SortArray(myArray)
Dim temp, i, j, i
For i = LBound(myArray, 2) To UBound(myArray, 2) - 1
For j = i + 1 To UBound(myArray, 2)
If myArray(1, i) myArray(1, j) Then '<<<<<<<<<<<<<<<<<<<<< HERE
For k = LBound(myArray, 1) To UBound(myArray, 1)
temp = myArray(k, i)
myArray(k, i) = myArray(k, j)
myArray(k, j) = temp
Next
End If
Next
Next
SortArray = myArray
End Function

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vqthomf" wrote in message
...
Change the to< to what?
Regards
Charles

"Bob Phillips" wrote:

Just change the to < in the 5th line of the function.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vqthomf" wrote in message
...
Thanks is it possible to do it in desc order?
regards
Charles

"Tom Ogilvy" wrote:

if you want to use a dynamic array and redim preserve, then you will
have to
use the approach you show.

Here is a bubble sort to sort on the first row ("column" in your
transposed
array)
Function SortArray(myArray)
Dim temp, i, j, i
For i = LBound(myArray, 2) To UBound(myArray, 2) - 1
For j = i + 1 To UBound(myArray, 2)
If myArray(1, i) myArray(1, j) Then
For k = LBound(myArray, 1) To UBound(myArray, 1)
temp = myArray(k, i)
myArray(k, i) = myArray(k, j)
myArray(k, j) = temp
Next
End If
Next
Next
SortArray = myArray
End Function

Here is a test function used to test it.
Put random data in A1:F2, then run the Tester macro

Sub Tester()
Dim myArray
myArray = Range("A1:F3").Value
myArray = SortArray(myArray)
Range("A1:F3") = myArray
End Sub

--
Regards,
Tom Ogilvy

"vqthomf" wrote:

I am having a problem with arrays I am trying to add to an array
with
3
columns, I had to use the code below but how can I sort it by the
first
column or is there another way of populating the array
Regards
Charles

ElimentCount = ElimentCount + 1
ReDim Preserve myArray(1 To 3, 1 To ElimentCount)

myArray(1, ElimentCount) = vqSize
myArray(2, ElimentCount) = vqRoot
myArray(3, ElimentCount) = fName