VBA Calculation in Excel

Question

Hi,

I have created a shipping cost calculator as per the shipping zone. I need to create a Macro for the same using User Form. I have already done but not able to get the calculation, I'm not very much familiar with VBA and using google help I created this much. Need help in completing it. You can either use the table reference or make a complete build - Calculator as given in the reference.

https://www.dropbox.com/s/bdelelnbw637pob/Macro%20Testing.xlsm?dl=0

Thanks,

Sunil

[Moved from: Office / Excel / Windows 10 / Office 365 Home]

Answer 1

Results

Answer 2

And when we change the billable weightagain without changing the shipping zoneit does not calculate unless we change the shipping zone.

Copy my code into your sample file and it works.

Andreas.

Answer 3

Thank you very much, Jeovany. 

It works perfectly. I used the drop down box and you made it more simple by adding a list box.

The C5 & C8 cells are used with excel and not VBA. In the original file, I had a box to enter the value and then calculated using the VBA code. Which calculated through the range of cells. I wanted to avoid that step, as the main calculation sheet needs to be hidden.

Jeovany. there is small change needed. 

After we enter the billable weight and change the shipping zone it gives the shipping cost. And when we change the billable weightagain without changing the shipping zoneit does not calculate unless we change the shipping zone.

Answer 4

Hi Sunil

Please, download fr****th the answer to your question

https://www.dropbox.com/s/pqf9hflduul5w89/Macro%20Testing.xlsm?dl=0

Do let me know if you need more help

Regards

Jeovany

Answer 5

Option Explicit

Dim C5 As Variant

Dim ShippingZones As Variant

Dim ShippingFeesMinCharge As Variant

Dim ShippingFeesIncremantal As Variant

Private Sub UserForm_Initialize()

  Dim Item As Variant

  'No idea where C5 comes from

  C5 = 0

  'Fill the Shipping defaults

  ShippingZones = Array("Local", "Intra-State", "Intra-Region", "Intra-Region Metros", "Intra-Region National", "Remote")

  ShippingFeesMinCharge = Array(110, 132, 165, 198, 242, 308)

  ShippingFeesIncremantal = Array(11, 13.2, 16.5, 19.8, 24.2, 30.8)

  'Fill the listbox

  For Each Item In ShippingZones

    Me.Shipping_location_Box1.AddItem Item

  Next

  'First zone as default

  Me.Shipping_location_Box1.ListIndex = 0

End Sub

Private Sub Weightinput1_Change()

  'Calculate the result when the weight changes

  Dim BillableWeight As Double, MinCharge As Double, Incremental As Double

  Dim ExtraWeight As Double, FuelSurcharge As Double, Discount As Double

  Dim Result As Double

  'Show an error in case of invalid input

  Me.ResultTextBox2 = "(invalid input)"

  If Not IsNumeric(Me.Weightinput1) Then Exit Sub

  BillableWeight = CDbl(Me.Weightinput1)

  If BillableWeight < 0 Then Exit Sub

  If Me.Shipping_location_Box1.ListIndex < 0 Then Exit Sub

  MinCharge = ShippingFeesMinCharge(Me.Shipping_location_Box1.ListIndex)

  'P5:  =IF(C5<10,"Yes","No")

  'R7:  =IF(P5="Yes",0,C5-10)

  'combined:

  '=IF(C5<10,0,C5-10)

  ExtraWeight = IIf(C5 < 10, 0, C5 - 10)

  'S9:  =IF(AND($Q$3=S$4,$R$7>0),($R$7*S$6),0)

  If ExtraWeight > 0 Then

    Incremental = ExtraWeight * ShippingFeesIncremantal(Me.Shipping_location_Box1.ListIndex)

  Else

    Incremental = 0

  End If

  'S11:  =S8+0.2*S8

  'S12:  =S9+0.2*S9

  'S13:  =SUM(S11:S12)

  'combined:

  '(S8+S9)*1.2

  FuelSurcharge = (MinCharge + Incremental) * 1.2

  'Q15:  =IF(AND(C5>99,C5<=499),0.25,IF(C5>499,0.3,0))

  If (C5 > 99) And (C5 <= 499) Then

    Discount = 0.25

  ElseIf C5 > 499 Then

    Discount = 0.3

  Else

    Discount = 0

  End If

  'S15:  =S13-($Q$15*S13)

  'S16:  =S15*1.15

  'combined:

  '=(S13-($Q$15*S13))*1.15

  Result = (FuelSurcharge - (FuelSurcharge * Discount)) * 1.15

  Me.ResultTextBox2 = Format(Result, "0.00")

End Sub

Private Sub Shipping_location_Box1_Change()

  'Calculate the result when the zone changes

  Weightinput1_Change

End Sub