Results 1 to 2 of 2

Thread: Actions by date

  1. #1
    Join Date
    May 2004

    Cool Unanswered: Actions by date


    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...


  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland
    hi Trombone

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

    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
                '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


Posting Permissions

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