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...
ive put this little bit of code together to show you how to insert formulas dependent on date,
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
'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"
hope this gives you an idea on how to solve your problem