Excel - 2003 VBA Code Error - Run time error '380':Invalid Property

[Vedant]

Dear Sir/Madam,

I new in this field and it?s my job first month, I am getting the following error. I would appreciate if you would help me. In excel-2003 VBA Code I am getting ?Run time error '380':Invalid Property?. I tried all possibilities but couldn?t succeed.

I have put ** in the code where I am getting error.

Thanks a lot
Vedant

[VBA]
'This subroutine updates
Sub runRenewals()
Dim RenewalDate As Date
Dim ReportDate As Date
Dim colRenewalCaseChar As Collection
Dim colPremium As Collection
Dim colClaims As Collection
Dim colMisc As Collection
Dim i As Long

RenewalDate = getRenewalDate
ReportDate = getReportDate

Set colRenewalCaseChar = getRenewalGroups(RenewalDate, ReportDate)
Range("EraseCaseChar").ClearContents
Range("ErasePremium").ClearContents
Range("EraseIncurredPaidClaims").ClearContents

Range("Control").Select
Sheet2.pbProgress.Min = 1
** Sheet2.pbProgress.Max = colRenewalCaseChar.Count ' Run time error '380':Invalid property


For i = 1 To colRenewalCaseChar.Count
Set colPremium = New Collection
Set colClaims = New Collection
Set colMisc = New Collection

Set colPremium = getGroupsPremium(colRenewalCaseChar(i).getGroupNumber)
Set colClaims = getGroupClaims(colRenewalCaseChar(i).getGroupNumber)
Set colMisc = getGroupsCaseChar(colRenewalCaseChar(i).getGroupNumber)
Call UpdateCaseChar(colRenewalCaseChar(i), i)
Call UpdatePremium(colPremium, i)
Call UpdateClaims(colClaims, i)
Call UpdateMisc(colMisc, i)
Sheet2.pbProgress.Value = i
Next i

Sheet1.Visible = True
Sheet3.Visible = True
Sheet7.Visible = True

End Sub

Sub UpdateCaseChar(MyCaseChars As CaseChar, i As Long)
Dim rCaseChar As Range
Set rCaseChar = Range("StartCaseChar")
rCaseChar(i, 1).Value = i
rCaseChar(i, 2).Value = MyCaseChars.getGroupNumber
rCaseChar(i, 3).Value = MyCaseChars.getDatabase
rCaseChar(i, 4).Value = MyCaseChars.getState
rCaseChar(i, 5).Value = MyCaseChars.getEmployerName
rCaseChar(i, 6).Value = MyCaseChars.getWritingAgent
rCaseChar(i, 7).Value = MyCaseChars.getGeneralAgent

rCaseChar(i, 8).Value = MyCaseChars.getOriginalEffectiveDate
rCaseChar(i, 9).Value = MyCaseChars.getTotalEEs
rCaseChar(i, 10).Value = MyCaseChars.getMembers
rCaseChar(i, 11).Value = MyCaseChars.getRTNB
rCaseChar(i, 12).Value = MyCaseChars.getNextRenewalDate
rCaseChar(i, 13).Value = MyCaseChars.getLastRenewalDate
rCaseChar(i, 14).Value = MyCaseChars.getLastIncrease
rCaseChar(i, 15).Value = MyCaseChars.getPlan
rCaseChar(i, 16).Value = MyCaseChars.getTakeover
rCaseChar(i, 17).Value = MyCaseChars.getSIC
rCaseChar(i, 18).Value = MyCaseChars.getDeductible
rCaseChar(i, 19).Value = MyCaseChars.getCommission
End Sub

Sub UpdatePremium(colPrem As Collection, i As Long)
Dim myobjPremium As Premium
Dim rPremium As Range
Dim dDate As Date

Dim c As Long
If colPrem.Count > 0 Then
Set rPremium = Range("StartPremium")
rPremium(i, 1).Value = i
rPremium(i, 2).Value = colPrem(1).getGroupNumber

For Each myobjPremium In colPrem
c = DateDiff("m", myobjPremium.getEarnedDate, Range("reportdate").Value)
rPremium(i, 3 + c).Value = myobjPremium.getEarnedCollectedPremium
Next
End If
End Sub

Sub UpdateClaims(MyClaims As Collection, i As Long)
Dim myobjClaims As Claims
Dim rClaims As Range
Set rClaims = Range("StartIncurredPaidClaims")
Dim c As Long
Dim d As Boolean
If MyClaims.Count > 0 Then
rClaims(i, 1).Value = i
d = False

For Each myobjClaims In MyClaims
If myobjClaims.getIncurredDate > 0 Then
c = DateDiff("m", myobjClaims.getIncurredDate, Range("reportdate").Value)
rClaims(i, 3 + c).Value = myobjClaims.getIncurredPaidClaims
If d = False Then
rClaims(i, 2).Value = myobjClaims.getGroupNumber
d = True
End If
End If
Next
End If
End Sub

Sub UpdateMisc(colMisc As Collection, i As Long)
Dim myobjCaseChar As CaseChar
Dim rMembers As Range
Dim rEmployees As Range
Dim rRTNB As Range
Dim rLastIncrease As Range

Dim dDate As Date

Dim c As Long
Set rMembers = Range("StartMembers")
Set rEmployees = Range("StartEEs")
Set rRTNB = Range("StartRTNB")
Set rLastIncrease = Range("StartLastIncrease")
If colMisc.Count > 0 Then
rMembers(i, 1).Value = i
rMembers(i, 2).Value = colMisc(1).getGroupNumber
rEmployees(i, 1).Value = i
rEmployees(i, 2).Value = colMisc(1).getGroupNumber
rRTNB(i, 1).Value = i
rRTNB(i, 2).Value = colMisc(1).getGroupNumber
rLastIncrease(i, 1).Value = i
rLastIncrease(i, 2).Value = colMisc(1).getGroupNumber

For Each myobjCaseChar In colMisc
c = DateDiff("m", myobjCaseChar.getReportDate, Range("reportdate").Value)
rMembers(i, 3 + c).Value = myobjCaseChar.getMembers
rEmployees(i, 3 + c).Value = myobjCaseChar.getTotalEEs
rRTNB(i, 3 + c).Value = myobjCaseChar.getRTNB
rLastIncrease(i, 3 + c).Value = myobjCaseChar.getLastIncrease
Next
End If
End Sub
[/VBA]

[mdmackillop]

Hi Vedant,
Welcome to VBAX.

Can you post a workbook with sample data? Use Manage Attachments in the Go Advanced reply section
Regards
MD

[Aussiebear]

Is there a blank cell within the collection (colRenewalCaseChar.Count) being counted?