if_elseif statement in VBA

Question

I am stuck with doing If .. Else statement for weeks. I am facing issues like "type mismatch", or when I run the code nothing appears in my cells. Please help me to solve this problem.

Sub ifstatement()

  Dim ws As Worksheet
  Set ws = ThisWorkbook.Sheets("SamplePO")
  Dim rg, rg1 As Range
  Set rg = ActiveSheet.Range("L17:L90")
  Set rg1 = ActiveSheet.Range("I17:I90")
  Dim pound As Double
  Dim nettwt As Double
  Dim grosswt As Double

  If rg < 130 Then
    grosswt = rg1 + 20

  ElseIf rg = 131 And rg <= 200 Then
    grosswt = (rg1 * 0.15) + 15

  ElseIf rg = 201 And rg <= 500 Then
    grosswt = rg1 * 0.11

  ElseIf rg = 501 And rg <= 999 Then
    grosswt = rg1 * 0.7

  ElseIf rg = 1000 And rg <= 2000 Then
    grosswt = rg1 * 0.5

  ElseIf rg = 2001 And rg <= 4999 Then
    grosswt = rg1 * 0.5

  ElseIf rg = 5000 And rg <= 8000 Then
    grosswt = rg1 * 0.5

  ElseIf rg = 8001 And rg <= 10000 Then
    grosswt = rg1 * 0.5

End If

End Sub

Answer 1

These modifications to your original code might solve your problem.
Your code has a few problems, some of what was mentioned by the fellow coders in the comments.
I suggest you go and research each one to get a understanding of why your code did not work.

    Sub ifstatement()

    Dim rg As Range
    Dim cell As Range
    Set rg = ActiveSheet.Range("L17:L90")
    Dim grosswt As Double

      For Each cell In rg.Cells
      grosswt = 0

        If cell <= 130 Then
          grosswt = cell.Offset(0, -3).Value + 20

        ElseIf cell >= 131 And cell <= 200 Then
          grosswt = (cell.Offset(0, -3).Value * 0.15) + 15

        ElseIf cell >= 201 And cell <= 500 Then
          grosswt = cell.Offset(0, -3).Value * 0.11

        ElseIf cell >= 501 And cell <= 999 Then
          grosswt = cell.Offset(0, -3).Value * 0.7

        ElseIf cell >= 1000 And cell <= 2000 Then
          grosswt = cell.Offset(0, -3).Value * 0.5

        ElseIf cell >= 2001 And cell <= 4999 Then
          grosswt = cell.Offset(0, -3).Value * 0.5

        ElseIf cell >= 5000 And cell <= 8000 Then
          grosswt = cell.Offset(0, -3).Value * 0.5

        ElseIf cell >= 8001 And cell <= 10000 Then
          grosswt = cell.Offset(0, -3).Value * 0.5

        End If
          cell.Offset(0, 1).Value = grosswt ' Will output the answer in column "M" from "M17:M90"
      Next cell

  End Sub

You can also (like mentioned by others in the comments) make use of your own function.
Create this function then in the cells where you want the answers type: =myfunc(I17,L17) then drag it down till you get to =myfunc(I90,L90)

  Function myfunc(check As Range, use As Range) As Double

    Dim grosswt As Double

        If check.Value <= 130 Then
          grosswt = use.Value + 20

        ElseIf check.Value >= 131 And check.Value <= 200 Then
          grosswt = (use.Value * 0.15) + 15

        ElseIf check.Value >= 201 And check.Value <= 500 Then
          grosswt = use.Value * 0.11

        ElseIf check.Value >= 501 And check.Value <= 999 Then
          grosswt = use.Value * 0.7

        ElseIf check.Value >= 1000 And check.Value <= 2000 Then
          grosswt = use.Value * 0.5

        ElseIf check.Value >= 2001 And check.Value <= 4999 Then
          grosswt = use.Value * 0.5

        ElseIf check.Value >= 5000 And check.Value <= 8000 Then
          grosswt = use.Value * 0.5

        ElseIf check.Value >= 8001 And check.Value <= 10000 Then
          grosswt = use.Value * 0.5

        End If

        myfunc = grosswt

  End Function

1. "You can't treat ranges with multiple cells as if they were single cells. You likely need some kind of loop here." - Time Williams
2. "^ what he said, + the fact that all your first conditions are checking for exact equality. You likely want to check if {a single number} is >=, instead of checking if {a range of variables} is =" - Bango
3. You never assign any cell to the calculated value.

Answer 2

Add this below UDF and use it in like built in formulas.
e.g. in the Gross Weight cell for row 7, =GetGrossWeight(I7,L7). Fill down formulas. Assuming cut off pounds be 130,200,500,1000.

Function GetGrossWeight(LBS As Range, NetWeight As Range) As Double
    Dim GrossWeight As Double
    Select Case CDbl(LBS.Value)
        Case Is <= 130:     GrossWeight = NetWeight.Value + 20
        Case Is <= 200:     GrossWeight = NetWeight.Value * 0.15 + 15
        Case Is <= 500:     GrossWeight = NetWeight.Value * 0.11
        Case Is <= 1000:    GrossWeight = NetWeight.Value * 0.7
        Case Else:          GrossWeight = NetWeight.Value * 0.5
    End Select
    GetGrossWeight = GrossWeight
End Function

Answer 3

Try using the countif function like below to see how many cells in the range meet the criteria.

  If application.worksheetfunction.countif(rg, "<130") >= 0 Then

You'll need to use a loop to do the operation on the range after the condition is met. Try the for loop like this:

for each cell in rng
cell.value = cell.value + 20 'you can put whatever operation you want to execute on the range here
next cell

Then you can go onto the next elseif following the same format, using countif to identify if the condition is met and the for loop to do the operation on the range of cells.

Hope that helps!