Microsoft Office Forums - View Single Post - [Solved] Macro to Skip Calcuation if Field is Blank?!

fumei · 04-06-2013, 06:58 PM

1. use formfield objects (docFF in code below)
2. use Select Case (for the cycle type) and then a test of Year (not = 0)

Option Explicit

Code:

 
Sub CalcPayable()
'
' CalcPayable Macro
Dim docFF As FormFields
Set docFF = docFF()
 
Select Case docFF("Cycle").Result
Case "Annually"
If docFF("Year1").Result <> 0 Then
docFF("Payable1").Result = docFF("Year1").Result
If docFF("Year2").Result <> 0 Then
docFF("Payable2").Result = docFF("Year2").Result
If docFF("Year3").Result <> 0 Then
docFF("Payable3").Result = docFF("Year3").Result
' same for year 4 and 5
Case "Semiannually"
If docFF("Year1").Result <> 0 Then
docFF("Payable1").Result = docFF("Year1").Result / 2
 
If docFF("Year2").Result <> 0 Then
docFF("Payable2").Result = docFF("Year2").Result / 2
 
If docFF("Year3").Result <> 0 Then
docFF("Payable3").Result = docFF("Year3").Result / 2
' same for year 4 and 5
Case "Quarterly"
If docFF("Year1").Result <> 0 Then
docFF("Payable1").Result = docFF("Year1").Result / 4
 
If docFF("Year2").Result <> 0 Then
docFF("Payable2").Result = docFF("Year2").Result / 4
 
If docFF("Year3").Result <> 0 Then
docFF("Payable3").Result = docFF("Year3").Result / 4
' same for year 4 and 5
Case "Bimonthly"
If docFF("Year1").Result <> 0 Then
docFF("Payable1").Result = docFF("Year1").Result / 6
 
If docFF("Year2").Result <> 0 Then
docFF("Payable2").Result = docFF("Year2").Result / 6
 
If docFF("Year3").Result <> 0 Then
docFF("Payable3").Result = docFF("Year3").Result / 6
' same for year 4 and 5
Case "Monthly"
If docFF("Year1").Result <> 0 Then
docFF("Payable1").Result = docFF("Year1").Result / 12
 
If docFF("Year2").Result <> 0 Then
docFF("Payable2").Result = docFF("Year2").Result / 12
 
If docFF("Year3").Result <> 0 Then
docFF("Payable3").Result = docFF("Year3").Result / 12
' same for year 4 and 5
 
Case "Triannually"
If docFF("Year1").Result <> 0 Then
docFF("Payable1").Result = docFF("Year1").Result / 3
 
If docFF("Year2").Result <> 0 Then
docFF("Payable2").Result = docFF("Year2").Result / 3
 
If docFF("Year3").Result <> 0 Then
docFF("Payable3").Result = docFF("Year3").Result / 3
' same for year 4 and 5
 
End Select
End Sub