Freight price in particular cell

Question

I want to bring our freight price in workbook 1, Cell B14.

The freight price varies based on freight weight:

If the freight weight is less than 45 kg, freight price is 55.00 HKD
If the weight is greater than 45 kg, freight price is 47.00 HKD
If the weight is greater than 100 kg, freight price is 29,50 HKD

The VBA I have so far is listed below, but I get the following error: statement invalid outside Type block. How to make this work?

Public Function freightrate()

    Weight As Double
    freightrate As Double

    Weight = Cells(B, 8).Value
    freightrate = Cells(B, 14).Value

    If Weight < 45 Then
        freightrate = 55
    ElseIf Weight > 45 < 100 Then
        freightrate = 47
    ElseIf Weight > 100 < 300 Then
        freightrate = 29,50
    End If

End Function

Hi Uday, sorry but we're not goint to solve the problem from scratch for you. Try to solve it and we will help make it work and make it efficient. — Timothée Bourguignon, Dec 07 '15 at 11:41
> I get error message for bellow stated programPublic Function freightrate() Weight As Double freightrate As Double Weight = Cells(B, 8).Value freightrate = Cells(B, 14).Value If Weight < 45 Then freightrate = 55 ElseIf Weight > 45 < 100 Then freightrate = 47 ElseIf Weight > 100 < 300 Then freightrate = 29,50 End If — Uday, Dec 07 '15 at 13:01
copy the code to your original question, format it correct and post also the error message; instead of "ElseIf Weight > 45 < 100" Then try "ElseIf Weight > 45 And Weight < 100 Then" — cboden, Dec 07 '15 at 13:12
same of course for the other comparisons where you have more then one value. BTW: it's better to use Select-Case instead of that If/ElseIf combinantions — cboden, Dec 07 '15 at 13:19

score 0 · Answer 1 · edited May 23 '17 at 11:52

0

Wah, my VBA time is wayyy back. I think you have 2 problems here, variable definition and scope.

1- To define variables you missed a Dim:

Dim Weight As Double

2- In VBA (see here) you use the name of the function as a return value. I think what is causing your trouble here is the

freightrate As Double

Remove it or use a temp variable and it should work.

edited May 23 '17 at 11:52

Community

1
1

answered Dec 07 '15 at 16:39

Timothée Bourguignon

2,190
3
23
39

score 0 · Answer 2 · answered Dec 08 '15 at 17:24

As cboden said - better to use SELECT CASE.
This can also be used as a worksheet function - =freightrate(Sheet1!B14) for example.
The function returns a VARIANT so that the ELSE statement can return the error - you may want to change it to CVErr(xlErrNum) to show a #Num! error instead - or change the return type to a Double if you want to do away with the error capture.
You could also use CASE > 300

Public Sub Test()

    Debug.Print freightrate(Range("B14").Value)

End Sub

Public Function freightrate(Weight As Double) As Variant

    Select Case Weight
        Case 45
            freightrate = 55
        Case 46 To 100
            freightrate = 47
        Case 100 To 300
            freightrate = 29.5
        Case Else
            freightrate = CVErr(xlErrValue)
    End Select


End Function

Freight price in particular cell

2 Answers2