lactoast2000


I am having a problem getting past this error. I can't seem to firgure out what I am doing wrong. I am new to VBA so that doesn't help, but I was wondering if anyone could help. Here is the code:

Sub Main()
Call Pull_Data
Call Input_Data
End Sub
Sub Pull_Data()

strWorkSheetRaw = "Jan07-May07"
Sheets(strWorkSheetRaw).Select
lRowCount = Cells(Rows.Count, "A").End(xlUp).Row

ReDim strETicketData(1 To lRowCount) As tETicketRaw
For lETicket = 1 To lRowCount Step 1
strETicketData(lETicket).strManuRawET = Sheets(strWorkSheetRaw).Cells(lETicket, 2)
strETicketData(lETicket).strModelRawET = Sheets(strWorkSheetRaw).Cells(lETicket, 3)
strETicketData(lETicket).strComTypeRawET = Sheets(strWorkSheetRaw).Cells(lETicket, 4)
strETicketData(lETicket).strSubTypeRawET = Sheets(strWorkSheetRaw).Cells(lETicket, 5)
strETicketData(lETicket).iCountRawET = Sheets(strWorkSheetRaw).Cells(lETicket, 6)
strETicketData(lETicket).dDateRawET = Sheets(strWorkSheetRaw).Cells(lETicket, 8)
Next lETicket
End Sub
Sub Input_Data()
Dim iColCounter As Integer, iRowCounter As Integer
iRowCounter = 1
iColCounter = 1
Do While iColCounter <= 6
Do While iRowCounter <= 60
lETicket = 1
Do While lETicket <= lRowCount
If strETicketData(lETicket).dDateRawET >= Sheets(Summary).Cells(323, 4) And strETicketData(lETicket).dDateRawET <= Sheets(Summary).Cells(323, 5) _
Then If strETicketData(lETicket).strModelRawET = Sheets(Summary).Cells(iRowCounter, "B") _
Then If strETicketData(lETicket).strComTypeRawET = Sheets(Summary).Cells(321, "A") _
Then lETSum = lETSum + strETicketData(lETicket).iCountRawET
Loop
Sheets(Summary).Cells(iRowCounter, 3) = lETSum
lETSum = 0
iRowCounter = iRowCounter + 1
Loop
iColCounter = iColCounter + 1
iRowCounter = 1
Loop
End Sub

The error comes when at the red portion of the code. Any thoughts




Re: Error 9: Subscript out of range

Navajo


1. Sheets(Summary) should be Sheets("Summary")

2. The nested If Then block should be

If strETicketData(lETicket).dDateRawET >= Worksheets("Summary").Cells(1, 4) And strETicketData(lETicket).dDateRawET <= Worksheets("Summary").Cells(1, 5) Then
If strETicketData(lETicket).strModelRawET = Worksheets("Summary").Cells(iRowCounter, "B") Then
If strETicketData(lETicket).strComTypeRawET = Worksheets("Summary").Cells(1, "A") Then lETSum = lETSum + strETicketData(lETicket).iCountRawET
End If
End If

3. lETicket = lETicket + 1 is required before Loop in the inner loop

4. Also consider using With...End With -

With Worksheets("Summary")
If strETicketData(lETicket).dDateRawET >= .Cells(1, 4) etc.
End If
End With