Sort a Record Structure within a Report Routine
 

Hi,
I want to be able to read some data into a record structure and then
depending on a users selection I want to be able to sort that structure
different ways..see the structure below....

Type ResourceProjectData
strResourceName As String
strPrimaryRole As String
intResourceTotalHours(1 To 12) As Integer
ProjectDetails(1 To 500) As ProjectData
End Type

So for instance the the data is read in sorted by ResourceName....I want to
be able to sort the record by PrimaryRole instead and then ResourceName....is
there anyway to do this programmatically or do I have to read out data to a
temporary sheet and the sort with worksheet functions ?

Thanks for any help

Sort a Record Structure within a Report Routine
 

Here is an example:

Type ProjectData
a As Long
b As Long
c As String
End Type

Type ResourceProjectData
strResourceName As String
strPrimaryRole As String
intResourceTotalHours(1 To 12) As Integer
ProjectDetails(1 To 500) As ProjectData
End Type

Sub ABC()
Dim n As Long
Dim r() As ResourceProjectData
Dim rTemp As ResourceProjectData
Dim i As Long, j As Long
n = 10
ReDim r(1 To n)
' populate the array
For i = 1 To n
PopData r(i)
Next

For i = 1 To n - 1
For j = i + 1 To n
If r(i).strPrimaryRole r(j).strPrimaryRole Then
rTemp = r(i)
r(i) = r(j)
r(j) = rTemp
End If
Next j
Next i
For i = 1 To n
Debug.Print r(i).strPrimaryRole
Next
End Sub

Sub PopData(rr As ResourceProjectData)
rr.strResourceName = Chr(Int(Rnd() * 26 + 65)) _
& Chr(Int(Rnd() * 26 + 65))
rr.strPrimaryRole = Chr(Int(Rnd() * 26 + 65))
For i = 1 To 12
rr.intResourceTotalHours(i) = Int(Rnd() * 100 + 1)
Next
For i = 1 To 500
rr.ProjectDetails(i).a = Int(Rnd() * 26 + 65)
rr.ProjectDetails(i).b = Int(Rnd() * 26 + 65)
rr.ProjectDetails(i).c = Chr(Int(Rnd() * 26 + 65))
Next
End Sub

--
Regards,
Tom Ogilvy


"scott56hannah" wrote:

Hi,
I want to be able to read some data into a record structure and then
depending on a users selection I want to be able to sort that structure
different ways..see the structure below....

Type ResourceProjectData
strResourceName As String
strPrimaryRole As String
intResourceTotalHours(1 To 12) As Integer
ProjectDetails(1 To 500) As ProjectData
End Type

So for instance the the data is read in sorted by ResourceName....I want to
be able to sort the record by PrimaryRole instead and then ResourceName....is
there anyway to do this programmatically or do I have to read out data to a
temporary sheet and the sort with worksheet functions ?

Thanks for any help

Sort a Record Structure within a Report Routine
 

Tom,

Thanks for that I will give it a go within my routine and see if I can get
it working...

Scott

"Tom Ogilvy" wrote:

Here is an example:

Type ProjectData
a As Long
b As Long
c As String
End Type

Type ResourceProjectData
strResourceName As String
strPrimaryRole As String
intResourceTotalHours(1 To 12) As Integer
ProjectDetails(1 To 500) As ProjectData
End Type

Sub ABC()
Dim n As Long
Dim r() As ResourceProjectData
Dim rTemp As ResourceProjectData
Dim i As Long, j As Long
n = 10
ReDim r(1 To n)
' populate the array
For i = 1 To n
PopData r(i)
Next

For i = 1 To n - 1
For j = i + 1 To n
If r(i).strPrimaryRole r(j).strPrimaryRole Then
rTemp = r(i)
r(i) = r(j)
r(j) = rTemp
End If
Next j
Next i
For i = 1 To n
Debug.Print r(i).strPrimaryRole
Next
End Sub

Sub PopData(rr As ResourceProjectData)
rr.strResourceName = Chr(Int(Rnd() * 26 + 65)) _
& Chr(Int(Rnd() * 26 + 65))
rr.strPrimaryRole = Chr(Int(Rnd() * 26 + 65))
For i = 1 To 12
rr.intResourceTotalHours(i) = Int(Rnd() * 100 + 1)
Next
For i = 1 To 500
rr.ProjectDetails(i).a = Int(Rnd() * 26 + 65)
rr.ProjectDetails(i).b = Int(Rnd() * 26 + 65)
rr.ProjectDetails(i).c = Chr(Int(Rnd() * 26 + 65))
Next
End Sub

--
Regards,
Tom Ogilvy


"scott56hannah" wrote:

Hi,
I want to be able to read some data into a record structure and then
depending on a users selection I want to be able to sort that structure
different ways..see the structure below....

Type ResourceProjectData
strResourceName As String
strPrimaryRole As String
intResourceTotalHours(1 To 12) As Integer
ProjectDetails(1 To 500) As ProjectData
End Type

So for instance the the data is read in sorted by ResourceName....I want to
be able to sort the record by PrimaryRole instead and then ResourceName....is
there anyway to do this programmatically or do I have to read out data to a
temporary sheet and the sort with worksheet functions ?

Thanks for any help

Sort a Record Structure within a Report Routine
 

Tom,

Thanks...I tried this last week and it worked very well and solved my
problem...thanks for your help

Scott

"scott56hannah" wrote:

Tom,

Thanks for that I will give it a go within my routine and see if I can get
it working...

Scott

"Tom Ogilvy" wrote:

Here is an example:

Type ProjectData
a As Long
b As Long
c As String
End Type

Type ResourceProjectData
strResourceName As String
strPrimaryRole As String
intResourceTotalHours(1 To 12) As Integer
ProjectDetails(1 To 500) As ProjectData
End Type

Sub ABC()
Dim n As Long
Dim r() As ResourceProjectData
Dim rTemp As ResourceProjectData
Dim i As Long, j As Long
n = 10
ReDim r(1 To n)
' populate the array
For i = 1 To n
PopData r(i)
Next

For i = 1 To n - 1
For j = i + 1 To n
If r(i).strPrimaryRole r(j).strPrimaryRole Then
rTemp = r(i)
r(i) = r(j)
r(j) = rTemp
End If
Next j
Next i
For i = 1 To n
Debug.Print r(i).strPrimaryRole
Next
End Sub

Sub PopData(rr As ResourceProjectData)
rr.strResourceName = Chr(Int(Rnd() * 26 + 65)) _
& Chr(Int(Rnd() * 26 + 65))
rr.strPrimaryRole = Chr(Int(Rnd() * 26 + 65))
For i = 1 To 12
rr.intResourceTotalHours(i) = Int(Rnd() * 100 + 1)
Next
For i = 1 To 500
rr.ProjectDetails(i).a = Int(Rnd() * 26 + 65)
rr.ProjectDetails(i).b = Int(Rnd() * 26 + 65)
rr.ProjectDetails(i).c = Chr(Int(Rnd() * 26 + 65))
Next
End Sub

--
Regards,
Tom Ogilvy


"scott56hannah" wrote:

Hi,
I want to be able to read some data into a record structure and then
depending on a users selection I want to be able to sort that structure
different ways..see the structure below....

Type ResourceProjectData
strResourceName As String
strPrimaryRole As String
intResourceTotalHours(1 To 12) As Integer
ProjectDetails(1 To 500) As ProjectData
End Type

So for instance the the data is read in sorted by ResourceName....I want to
be able to sort the record by PrimaryRole instead and then ResourceName....is
there anyway to do this programmatically or do I have to read out data to a
temporary sheet and the sort with worksheet functions ?

Thanks for any help