CODEWITHSUNDEEP
excelvba
user2736412
user2736412

Reputation: 31

If statement has me stumped

This is my first post ever and I am extremely new to vba so please be gentle. I have created a user form that has Territory (TextBox5), Weight (TextBox1), City (TextBox3) and Cost (TextBox4). This is a form to calculate the cost of shipping with a certain trucking company. There are rates that are associated with the weight AND THE City. If the city is Within the Territory there is a rate per 100lbs up to a max of 9000lbs. However, if the rate is Outside Of Territory then there is a higher rate per 100lbs up to a max of 9000lbs. Within Territory has a minimum of $15.00 and Outside of Territory has a minimum of $20.00. This is just on of the ways i have tried....

 Private Sub TextBox4_Change()
     Dim A As Single
     A = Val(TextBox1.Text)
     If (TextBox5.Text = "Within Territory" And TextBox1.Text <= 233) Then
         TextBox4.Text = TextBox1.Text * 6.5 / 100 * 1.3
         If (TextBox5.Text = "Out of Territory" And TextBox1.Text <= 200) Then
             TextBox4.Text = TextBox1.Text * 10 / 100 * 1.3
         End If
    End If       
End Sub

I wanted to run through and put all of the rates in at the different weights and all but i couldn't even get this part to work. The division of 100 is for the per 100lbs. and the 1.3 is the 30% fuel surcharge.

rates for within territory are as follows... Minimum 15.00....6.50 per 100 up to 999lbs...6.00 from 1000 up to 1999 lbs....from 2000 up to a maximum of 9000 lbs. is 5.50

for outside of territory rates are: min of 20.00...the same in lbs. frequency is 10.00, 9.25, 8.00.

any help or suggestions in the right direction will be GREATLY appreciated!! Thanks!

Upvotes: 3

Views: 121

Answers (3)

EagleEye222
EagleEye222

Reputation: 1

If I understand the main pieces of you problem, here is what I would use as a basis to determine the costs. See the steps that I have entered below:

Private Sub TextBox4_Change() Dim AX As Single AX = Val(textBox1.Text) If (TextBox5.Text = "Within Territory") Then Select Case Val(textBox1.Text) Case 100 To 999 Val(TextBox4.Text) = AX * 6.5 / 100 * 1.3 Case 1000 To 1999 Val(TextBox4.Text) = AX * 6# / 100 * 1.3 Case 2000 To 9000 Val(TextBox4.Text) = AX * 5.5 / 100 * 1.3 Case Else End Select

ElseIf (TextBox5.Text = "Out of Territory") Then
    Select Case Val(textBox1.Text)
    Case 100 To 999
        Val(TextBox4.Text) = AX * 10 / 100 * 1.3
    Case 1000 To 1999
        Val(TextBox4.Text) = AX * 9.5 / 100 * 1.3
    Case 2000 To 9000
        Val(TextBox4.Text) = AX * 8 / 100 * 1.3
    Case Else
    End Select
End If

End Sub

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53166

Taking a bit more structured approach will help.

  • Separate your data from your logic (makes it easier to maintain)
  • Use something like If weight in first band Then calculate rate Else consider next band
  • I don't think you want to do this on TextBox4 change (cost). Probably on any other textbox change.

Try this (call from Territory or Weight textbox change events)

Private Sub TextBox4_Change()
    Dim weight As Single
    Dim cost As Single

    Dim rates(1 To 3) As Single
    Dim bands(1 To 3) As Single
    Dim min As Single
    Dim surcharge As Single

    weight = Val(TextBox1.Text)

    If weight <= 0# Then
        ' deal with invalid weight
        TextBox4.Text = "Invalid Weight"
        Exit Sub
    End If

    If TextBox5.Text = "Within Territory" Then
        rates(1) = 6.5
        rates(2) = 6#
        rates(3) = 5.5
        min = 15#
    ElseIf TextBox5.Text = "Out of Territory" Then
        rates(1) = 10#
        rates(2) = 9.25
        rates(3) = 8#
        min = 20#
    Else
        Exit Sub
    End If
    bands(1) = 1000#
    bands(2) = 2000#
    bands(3) = 9000#
    surcharge = 1.3
    If weight > bands(3) Then
        TextBox4.Text = "Weight too big"
        Exit Sub
    End If

    If weight < bands(1) Then
        cost = weight * rates(1) / 100#
    Else
        cost = bands(1) * rates(1) / 100#
        If weight < bands(2) Then
            cost = cost + (weight - bands(1)) * rates(2) / 100#
        Else
            cost = cost + (bands(2) - bands(1)) * rates(2) / 100#
            If weight <= bands(3) Then
                cost = cost + (weight - bands(2)) * rates(3) / 100#
            End If
        End If
    End If
    cost = cost * surcharge
    If cost < min Then cost = min

    TextBox4.Text = Format(cost, "#,##0.00")
End Sub

Upvotes: 1

cuabanana
cuabanana

Reputation: 126

If this language is a strong-typed language like java, you would need to explicitly change the text to integers to be able to calculate the prices. It also appears that your scale is a sliding scale. If you do not want the numbers to decrease, you would have to add the initial amount ($15 for within territory, $20 for outside territory) to the values that you get. In that case, you could use an if-else statement as follows (this is pseudo-code, not actual vba text): If (Text Box 5 = "Within territory") then str2num (Text Box 4)+= 15. Then you would do the other multiplications and additions as needed inside the loop. else, str2num (Text Box 4) += 20 and do your other calculations from there. Hopefully this helps.

Upvotes: 0

Related Questions