Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    How to add years into this vba code?

    Hi guys,

    I am hoping someone could help me make this code work a bit differently. Currently it calculates the monthly totals in a excel sheet but the problem occoured when we entered a new year. I would like to change the VBA so that is takes the year into account as well. Any suggestion is welcome!


    Code:
    Public Function MProfit(mnth As String) As Currency
    
        Application.Volatile
        mon = Month([mnth] & " 1, 2015")
        MProfit = 0
    
    
        For I = 2 To 5000 Step 24
            For J = 7 To 28 Step 7
                If Not IsDate(Cells(I, J)) Then Exit Function
                If Month(Cells(I, J)) = mon Then
                    MProfit = MProfit + Cells(I + 22, J)
                End If
            Next J
        Next I
    
    
    End Function

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Something like this:

    Code:
    Public Function MProfit(mnth As String, lYear As Long) As Currency
    
        Application.Volatile
        mon = Month([mnth] & " 1, 2015")
        MProfit = 0
    
    
        For I = 2 To 5000 Step 24
            For J = 7 To 28 Step 7
                If Not IsDate(Cells(I, J)) Then Exit Function
                If Month(Cells(I, J).Value) = mon And Year(Cells(I, J).Value) = lYear Then
                    MProfit = MProfit + Cells(I + 22, J).Value
                End If
            Next J
        Next I
    
    
    End Function
    Not sure why you'd use a volatile UDF for this though.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Something like this:

    Code:
    Public Function MProfit(mnth As String, lYear As Long) As Currency
    
        Application.Volatile
        mon = Month([mnth] & " 1, 2015")
        MProfit = 0
    
    
        For I = 2 To 5000 Step 24
            For J = 7 To 28 Step 7
                If Not IsDate(Cells(I, J)) Then Exit Function
                If Month(Cells(I, J).Value) = mon And Year(Cells(I, J).Value) = lYear Then
                    MProfit = MProfit + Cells(I + 22, J).Value
                End If
            Next J
        Next I
    
    
    End Function
    Not sure why you'd use a volatile UDF for this though.
    This bit of code wasn't created by me and have to admit I am not confident with using it.

    Would you have an easy-er suggestion? Also what is "volatile UDF"?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    UDF = User Defined Function
    Volatile means that it recalculates whenever the workbook does, even if none of its inputs have actually changed.

    It appears to me that SUMIF or SUMPRODUCT formulas would achieve the same thing as your UDF does.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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