1. Registered User
Join Date
Oct 2013
Posts
165

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.

"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. Moderator
Join Date
Mar 2009
Posts
5,442
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```

4. Registered User
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```

5. Moderator
Join Date
Mar 2009
Posts
5,442
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```

6. Registered User
Join Date
Oct 2013
Posts
165
Thank you very much Sinndho. That solved my problem.

7. Moderator
Join Date
Mar 2009
Posts
5,442
You're welcome!

8. Registered User
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.

9. Moderator
Join Date
Mar 2009
Posts
5,442
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```

10. Registered User
Join Date
Oct 2013
Posts
165
Thank you very much Sinndho.

It worked fine.

11. Moderator
Join Date
Mar 2009
Posts
5,442
You're welcome!

12. Registered User
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```

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
why do it this way?

use a calendar table which gives you this functionality

14. Registered User
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```

15. Registered User
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.

Posting Permissions

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