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:

Range("J25").Value = myrate

to

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

[steve400243]

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

Range("J25").Value = myrate

to

That did it, thank you - I appreciate it.

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

[steve400243]

Thanks for the help Md - I appreciate it.