Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2013
    Posts
    165

    Answered: Simplification in VBA Code

    Dear Seniors,

    I had created the following code for calculating the Mobilization month in my access database. It works fine however I would like to seek your help to simplify the code since I would like to expand this monthly code to Weekly Code.

    Code:
    Public Function Mob_Start(D1 As Double, D2 As Double, D3 As Double, D4 As Double, D5 As Double, D6 As Double, D7 As Double, D8 As Double, D9 As Double, D10 As Double, D11 As Double, D12 As Double, D13 As Double, D14 As Double, D15 As Double) As String
    
    If D1 <> 0 Then
       Mob_Start = "M01"
    
    ElseIf D2 <> 0 Then
       Mob_Start = "M01"
    
    ElseIf D3 <> 0 Then
       Mob_Start = "M02"
    
    ElseIf D4 <> 0 Then
       Mob_Start = "M03"
       
    ElseIf D5 <> 0 Then
       Mob_Start = "M04"
    
    ElseIf D6 <> 0 Then
       Mob_Start = "M05"
    
    ElseIf D7 <> 0 Then
       Mob_Start = "M06"
       
    ElseIf D8 <> 0 Then
       Mob_Start = "M07"
       
    ElseIf D9 <> 0 Then
       Mob_Start = "M08"
       
    ElseIf D10 <> 0 Then
       Mob_Start = "M09"
       
    ElseIf D11 <> 0 Then
       Mob_Start = "M10"
       
    ElseIf D12 <> 0 Then
       Mob_Start = "M11"
       
    ElseIf D13 <> 0 Then
       Mob_Start = "M12"
    
    ElseIf D14 <> 0 Then
       Mob_Start = "M13"
       
    ElseIf D15 <> 0 Then
       Mob_Start = "M14"
    
    Else
       Mob_Start = "NA"
    
    End If
    
    End Function
    Where D1 to D15 are numbers which will be entered by me based on Project requirement. Once I got the Month number I will calculate the mobilization date with help of other table.
    Thanks and Regards
    R. Vadivelan

  2. Best Answer
    Posted by Sinndho

    "This should work:
    Code:
    Public Function NewMob_Start(ParamArray D() As Variant) As String
    
        Dim i As Long
        
        NewMob_Start = "NA"
        For i = 0 To 14
            If IsMissing(D(i)) Then Exit For
            If D(i) <> 0 Then
                NewMob_Start = "M" & Format(i, "00")
                Exit For
            End If
        Next i
            
    End Function
    "


  3. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 15
    This should work:
    Code:
    Public Function NewMob_Start(ParamArray D() As Variant) As String
    
        Dim i As Long
        
        NewMob_Start = "NA"
        For i = 0 To 14
            If IsMissing(D(i)) Then Exit For
            If D(i) <> 0 Then
                NewMob_Start = "M" & Format(i, "00")
                Exit For
            End If
        Next i
            
    End Function
    Have a nice day!

  4. #3
    Join Date
    Oct 2013
    Posts
    165
    Thank you very much Sinndho.

    That code works fine. Could you please explain me how can I modify the formula to calculate the Mobilization finish month. The following formula I had used to calculate the Finish Month earlier.
    Code:
    Public Function Mob_Finish(D1 As Double, D2 As Double, D3 As Double, D4 As Double, D5 As Double, D6 As Double, D7 As Double, D8 As Double, D9 As Double, D10 As Double, D11 As Double, D12 As Double, D13 As Double, D14 As Double, D15 As Double) As String
    
    If D15 <> 0 Then
       Mob_Finish = "M14"
    
    ElseIf D14 <> 0 Then
       Mob_Finish = "M13"
    
    ElseIf D13 <> 0 Then
       Mob_Finish = "M12"
    
    ElseIf D12 <> 0 Then
       Mob_Finish = "M11"
       
    ElseIf D11 <> 0 Then
       Mob_Finish = "M10"
    
    ElseIf D10 <> 0 Then
       Mob_Finish = "M09"
    
    ElseIf D9 <> 0 Then
       Mob_Finish = "M08"
       
    ElseIf D8 <> 0 Then
       Mob_Finish = "M07"
       
    ElseIf D7 <> 0 Then
       Mob_Finish = "M06"
       
    ElseIf D6 <> 0 Then
       Mob_Finish = "M05"
       
    ElseIf D5 <> 0 Then
       Mob_Finish = "M04"
       
    ElseIf D4 <> 0 Then
       Mob_Finish = "M03"
       
    ElseIf D3 <> 0 Then
       Mob_Finish = "M02"
    
    ElseIf D2 <> 0 Then
       Mob_Finish = "M01"
    
    ElseIf D1 <> 0 Then
       Mob_Finish = "M01"
    
    Else
       Mob_Finish = "NA"
    
    End If
    
    End Function
    Thanks and Regards
    R. Vadivelan

  5. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 15
    Simply count top-down:
    Code:
    Public Function NewMob_Finish(ParamArray D() As Variant) As String
    
        Dim i As Long
        
        NewMob_Start = "NA"
        For i = 14 To 0 Step -1
            If IsMissing(D(i)) Then Exit For
            If D(i) <> 0 Then
                NewMob_Finish= "M" & Format(i, "00")
                Exit For
            End If
        Next i
            
    End Function
    Have a nice day!

  6. #5
    Join Date
    Oct 2013
    Posts
    165
    Thank you very much Sinndho. That solved my problem.
    Thanks and Regards
    R. Vadivelan

  7. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 15
    You're welcome!
    Have a nice day!

  8. #7
    Join Date
    Oct 2013
    Posts
    165
    Dear Sinndho,

    With reference to your above code I can use the same in my project to calculate the Months. However I getting the error in the formula result since I deleted the 1 month data from my file. (i.e) M00 data is deleted and instead of 14 months now I have only 13 months to calculate the start and end month.

    Code:
    Public Function NewMob_Start(ParamArray D() As Variant) As String
    
        Dim i As Long
        
        NewMob_Start = "NA"
        For i = 0 To 13
            If IsMissing(D(i)) Then Exit For
            If D(i) <> 0 Then
                NewMob_Start = "M" & Format(i, "00")
                Exit For
            End If
        Next i
            
    End Function
    But I am getting the value error in my file.
    Thanks and Regards
    R. Vadivelan

  9. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 15
    If you removed M00, the initialization of the loop must be:
    Code:
    For i = 1 To 14
    ...
    You can also use:
    Code:
        For i = 0 To 13
            If IsMissing(D(i)) Then Exit For
            If D(i) <> 0 Then
                NewMob_Start = "M" & Format(i + 1, "00")
                Exit For
            End If
        Next i
    Have a nice day!

  10. #9
    Join Date
    Oct 2013
    Posts
    165
    Thank you very much Sinndho.

    It worked fine.
    Thanks and Regards
    R. Vadivelan

  11. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 15
    You're welcome!
    Have a nice day!

  12. #11
    Join Date
    Oct 2013
    Posts
    165
    Dear Sinndho,

    I was using the following code for finding the mobilization month in my project and it was working fine. But now i have a different situation where my mobilization starts from -M06 to M60. This does not include M00.

    Code:
    Public Function NewMob_Start(ParamArray D() As Variant) As String
    
        Dim i As Long
        
        NewMob_Start = "NA"
        For i = 0 To 14
            If IsMissing(D(i)) Then Exit For
            If D(i) <> 0 Then
                NewMob_Start = "M" & Format(i+1, "00")
                Exit For
            End If
        Next i
            
    End Function
    I had tried to modify the code but not succeded could you please help me solve this issue
    Thanks and Regards
    R. Vadivelan

  13. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why do it this way?

    use a calendar table which gives you this functionality
    I'd rather be riding on the Tiger 800 or the Norton

  14. #13
    Join Date
    Oct 2013
    Posts
    165
    Dear Sinndho,

    Could you please help me to solve the problem as above. I already fixed the code little bit as below and it works fine. My current manning plan starts from -M05 to M59 and it includes M00. This function was working perfectly, but I have a strange problem for some people (very few) where the Man hours end on M19 and later mobilized again in M58 for As built Works, however the function shows only M19.

    Code:
    Public Function NewMob_Finish(ParamArray D() As Variant) As String
    
    Dim i As Long
        
        NewMob_Finish = "NA"
        For i = 59 To 0 Step -1
            If IsMissing(D(i)) Then Exit For
            If D(i) <> 0 Then
                NewMob_Finish = "M" & Format(i - 6, "00")
                Exit For
            End If
            Next i
            
    End Function
    Please help me to fix this.
    Thanks and Regards
    R. Vadivelan

  15. #14
    Join Date
    Oct 2013
    Posts
    165
    Dear Healdem,

    I tried to use the calendar table, But it works fine if I you use array formulas. I am sharing the workbook with other users, so I can't use the array formulas. I tried to use the vlookup with min and max but it does not works.
    Thanks and Regards
    R. Vadivelan

Posting Permissions

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