code not calculating storage charge correctly

[steve400243]

Hello I use the below code to calculate storage charge's after 7 days. It is not calculating correctly After the first month, it would need to be another $40.00 or 0.04 per pound. after 30 calendar days. C4 is the Pickup Date, and A12 is the delivery date, H10 is the Weight in Pounds. Please help me figure this out, I have played around with the code enough.

Private Sub Worksheet_Calculate()
   Dim myDays       As Long
   Dim myRate       As Double
   Application.EnableEvents = False
   myDays = DateDiff("d", Range("C4").Value, Range("A12").Value)
   If myDays >= 7 Then
      myRate = Range("H10") * 0.04
      If myRate < 40# Then myRate = 40#
   End If
   Range("J25").Value = myRate
   Application.EnableEvents = True


   'MsgBox myDays
End Sub

[mdmackillop]

You have no option for MyDays < 7

[steve400243]

You have no option for MyDays < 7

That's where i'm getting confused. I tried wording that under the "If my days >=7 then" line

[mdmackillop]

Something like

If myDays >= 7 Then
        myRate = Range("H10") * 0.04
        If myRate < 40# Then myRate = 40#
    Else
        myRate = Range("H10") * 0.1
        If myRate < 50# Then myRate = 50#
    End If

[mdmackillop]

BTW
If you disable events and the code crashes, events will be left disabled which could have consequences elsewhere. You can guard against that as follows

On Error GoTo Exits
    Application.EnableEvents = False

'Do stuff here
    
Exits:
    Application.EnableEvents = True

[offthelip]

I think all you need to do is change this line:

[vba]Range("J25").Value = myrate
[/vba]
to

[vba]Range("J25").Value = myrate + myrate * Application.WorksheetFunction.RoundDown(mydays / 30, 0)
[/vba]

[steve400243]

I think all you need to do is change this line:

[vba]Range("J25").Value = myrate
[/vba]
to

That did it, thank you - I appreciate it.

[vba]Range("J25").Value = myrate + myrate * Application.WorksheetFunction.RoundDown(mydays / 30, 0)
[/vba]

[steve400243]

Thanks for the help Md - I appreciate it.