Help with collections and types.

[manonfire32]

Hi everyone,

I am trying to use a collection and type to gather data from 2 sheets and output it on the third sheet. I believe I have some syntax errors and any help fixing them would be appreciated. The following is my code:


Type Person


name As String
eeid As String
ot As Single
reg As Single


End Type




Public Sub Readdatafile()






Dim row As Integer
Dim p As Person
Dim ptemp As Person
Dim company As Collection


Dim comp() As Person








For row = 1 To 50
If Cells(row, 1) = "TOTALS" Then


'if row is timecard/total
'read row and set values

p.eeid = Cells(row, 3).Value
p.name = Cells(row, 2).Value
p.reg = Cells(row, 5).Value
p.ot = Cells(row, 6).Value

'check if person is already in the collection
found = False
Index = 0
For Index = 1 To company.Count
Set ptemp = company(Item)
If ptemp.name = p.name Then
found = True
Index = Item
break
End If
Next row

If found Then
'increment the person's reg and ot time.
Set ptemp = company(Index)
ptemp = ptemp.reg + p.reg
Else 'person is not in the collection, add person
company.Add p
End If
Next


'now data is filled


'fill in headers here
Cells(1, 1).Value = "EEID"
Cells(1, 2).Value = "DET"
Cells(1, 3).Value = "DET CODE"
Cells(1, 4).Value = "HOURS"


row = 2
For Item = 1 To company.Count
Set ptemp = company(Item)

Sheet3.Cells(row, 1).Value = ptemp.eeid
Sheet3.Cells(row, 2).Value = "E"
Sheet3.Cells(row, 3).Value = "REG"
Sheet3.Cells(row, 4).Value = ptemp.reg
row = row + 1

If (company(Item).ot > 0) Then
Sheet3.Cells(row, 1).Value = ptemp.eeid
Sheet3.Cells(row, 2).Value = "E"
Sheet3.Cells(row, 3).Value = "OT"
Sheet3.Cells(row, 4).Value = ptemp.ot
row = row + 1
End If


End Sub