Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Jul 25, 2022
Solved

Dynamic macro to calculate the monthly receipts based on variables and nth number of months

How can i create a dynamic macro which could calculate the monthly receipts based on the highlighted in yellow variables?   Those that are highlighted in orange are supposed to populate the values ...
  • HansVogelaar's avatar
    HansVogelaar
    Jul 25, 2022

    hrh_dash 

    Does this work for you?

        Dim amortisecollection As String
        Dim contractbid As Range
        Dim contractterm As Range
        Dim period As Range
        Dim bidamount As Double
        Dim term As Long
    
        amortisecollection = InputBox("Are we billing the customer on a monthly basis? Enter Y or N")
        If amortisecollection = "Y" Then
            With ws.Cells
                Set contractbid = .Find(What:="Contract Bid (incl GST)", After:=ActiveCell, LookIn:=xlValues, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False)
                bidamount = contractbid.Offset(0, 1).Value
                Set contractterm = .Find(What:="Contact Term (months)", After:=ActiveCell, LookIn:=xlValues, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False)
                term = contractterm.Offset(0, 1).Value
                Set period = .Find(What:="Period", After:=ActiveCell, LookIn:=xlValues, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False)
                period.Offset(1).Resize(1000).ClearContents
                period.Offset(1).Resize(term).Value = bidamount / term
            End With
        Else
            MsgBox "Please key in the receipts and payments manually."
            Exit Sub
        End If

Resources