1. Registered User
Join Date
May 2004
Posts
17

Hello,

Does anyone know how I can program Excel, using VBA-code, to let it calculate certain formulas based upon predetermined dates.
F.e. : A certain amount needs to be capitalised from date A till B. Between A en B it's possible that there are some mouvements on the capital, like withdrawels, montly costs, etc...
To visualize this :
In sheet 1 : let's put all the info : the amount, the interest, start_date, end _date, withdrawel of.... (amount)on the...(date, between start and end) and every first of the month between the start and and date an administrative cost for the capitalisationcontract of 1.
In sheet 2 : the formulas needed to calculate the capitalisation
In sheet 3 : the output : Excel takes the start_date and copies the formulas in the logic order based upon the dates on which a certain calculation needs to be made...
Has anyone have any ideas how to code this...

Thanks.

2. Registered User
Join Date
Jan 2004
Location
Aberdeen, Scotland
Posts
1,067
hi Trombone

ive put this little bit of code together to show you how to insert formulas dependent on date,

Code:
```Sub test()
Dim i As Integer, Lastrow As Integer
Dim MyDate As Variant
Dim myDay As Integer

'get the lastrow with data in it
Lastrow = Range("D" & Rows.Count).End(xlUp).Row

For i = 1 To Lastrow
MyDate = Range("D" & i).Formula
'try to convert to date to see if this works
On Error Resume Next
MyDate = CDate(MyDate)
'if error occurs display message and write error on the worksheet
If Err.Number <> 0 Then
MsgBox "cell D" & i & " is not a date"
Range("E" & i).Formula = "Error" & Error(Err.Number)
On Error GoTo 0
Else
'otherwise put formula's on worksheet dependent on the day
On Error GoTo 0
'get day from the date
myDay = Day(MyDate)
Select Case myDay
Case 1 To 10
'do something here i will put in a
Range("E" & i).Formula = "a"
Case 11 To 20
Range("E" & i).Formula = "b"
Case 21 To 31
Range("E" & i).Formula = "c"
End Select
End If
Next i
End Sub```
hope this gives you an idea on how to solve your problem

Dave

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•