VBA Calculation

[AnnieM]

Hi Everyone,

I have a userform with a textbox, the contents of which are determined by a formula applied to other textboxes and if certain conditions are met. The textbox that this calculation applies to is "TextPremium"

The gist of it is that I want to be able to calculate the increase in a cost if some jobs are done at higher than normal rates (i.e. out of hours)

I am struggling to get this to work at all and would be grateful if someone can help with this:




Private Sub PremiumCalc()


Dim Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10 As Double


If Text1.Value <> "" Then
Var3 = Text1.Value
Else: Var3 = 0
If Text2.Value <> "" Then
Var4 = Text2.Value
Else: Var4 = 0
If Text3.Value <> "" Then
Var5 = Text3.Value
Else: Var5 = 0
If Text4.Value <> "" Then
Var6 = Text4.Value
Else: Var6 = 0
If Text7.Value <> "" Then
Var7 = Text7.Value
Else: Var7 = 0
If Text8.Value <> "" Then
Var8 = Text8.Value
Else: Var8 = 0
If TextTotalB.Value <> "" Then
Var9 = TextTotalB.Value
Else: Var9 = 0

Var1 = (Me.TextTotalA + VarB)
Var10 = (Var3 + Var4 + Var5 + Var6 + Var7 + Var8)
Var2 = ((Var1 - Var10) * TextRate1.Value)
Me.TextPremium.Value = (Var2 + (Var3 * TextRate2.Value) + (Var4 * TextRate2.Value) + _
(Var5 * TextRate3.Value) + (Var6 * TextRate4.Value) + (Var7 * TextRate5.Value) + (Var8 * TextRate6.Value))

End If
End If
End If
End If
End If
End If
End If
End Sub

Many thanks in advance

[mancubus]

?



Private Sub PremiumCalc()

Dim Var1 As Double, Var2 As Double, Var3 As Double, Var4 As Double, Var5 As Double
Dim Var6 As Double, Var7 As Double, Var8 As Double, Var9 As Double, Var10 As Double

Var3 = IIf(Text1.Value <> "", Text1.Value, 0)
Var4 = IIf(Text2.Value <> "", Text2.Value, 0)
Var5 = IIf(Text3.Value <> "", Text3.Value, 0)
Var6 = IIf(Text4.Value <> "", Text4.Value, 0)
Var7 = IIf(Text7.Value <> "", Text7.Value, 0)
Var8 = IIf(Text8.Value <> "", Text8.Value, 0)
Var9 = IIf(TextTotalB.Value <> "", TextTotalB.Value, 0)

Var1 = (Me.TextTotalA + VarB)
Var10 = (Var3 + Var4 + Var5 + Var6 + Var7 + Var8)
Var2 = ((Var1 - Var10) * TextRate1.Value)
Me.TextPremium.Value = (Var2 + (Var3 * TextRate2.Value) + (Var4 * TextRate2.Value) + _
(Var5 * TextRate3.Value) + (Var6 * TextRate4.Value) + (Var7 * TextRate5.Value) + (Var8 * TextRate6.Value))

End Sub

[mancubus]

you may need to convert the values in the texboxes to numbers.


Var3 = IIf(Text1.Value <> "", CDbl(Text1.Value), 0)
or

Var3 = IIf(Text1.Value <> "", Val(Text1.Value), 0)

[AnnieM]

Hi,

Awesome!

I've spent the entire day trying various methods to achieve this :banghead:

I will just run through all of the permutations, but this solution looks right to me. It puts a number into the TextBox anyway, which is more than I accomplished!

Thank you very much,

Anne

[mancubus]

you are welcome.

thanks for the feed back and marking the thread as solved.