Stacked If statements

rsutton1981

New Member
Joined
Mar 9, 2016
Messages
47
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have some code below. it works up to the line calculate cost where it just returns the value from the first if statement. Please help

Code:
Sub data_sorting()
  Dim Bcell As Range
    Dim NextRow
Dim I As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
'Jan
Sheets("Jan").Range("A2:AA10000").Clear
        For Each Bcell In Sheets("Data Input").Range("Az15", Sheets("Data Input").Range("Az" & Rows.Count).End(xlUp))
            If Bcell.Value = "Jan" Then
                NextRow = Sheets("Jan").Range("A" & Rows.Count).End(xlUp).row + 1
                Sheets("Jan").Range("A" & NextRow) = Sheets("Data Input").Range("G" & Bcell.row)
                Sheets("Jan").Range("B" & NextRow) = Sheets("Data Input").Range("AB" & Bcell.row)
                Sheets("Jan").Range("C" & NextRow) = Sheets("Data Input").Range("P" & Bcell.row)
              
'calculate paper size
If Sheets("Jan").Range("C" & NextRow) >= 0.55 Then
Sheets("Jan").Range("d" & NextRow) = "A0"
ElseIf Sheets("Jan").Range("C" & NextRow) >= 0.27 Then
Sheets("Jan").Range("d" & NextRow) = "A1"
ElseIf Sheets("Jan").Range("C" & NextRow) <= 0.26 Then
Sheets("Jan").Range("d" & NextRow) = "A2"
 
ElseIf Sheets("Jan").Range("C" & NextRow) <= 0.13 Then
Sheets("Jan").Range("d" & NextRow) = "A3"
 
  End If
'calculate cost
If Sheets("Jan").Range("D" & NextRow) = "A1" & Sheets("Jan").Range("B" & NextRow) <= 30 Then
Sheets("Jan").Range("E" & NextRow) = "2.5"
ElseIf Sheets("Jan").Range("D" & NextRow) = "A1" & Sheets("Jan").Range("b" & NextRow) >= 31 Then
Sheets("Jan").Range("E" & NextRow) = "5"
ElseIf Sheets("Jan").Range("D" & NextRow) = "A1" & Sheets("Jan").Range("b" & NextRow) >= 61 Then
Sheets("Jan").Range("E" & NextRow) = "8"
       
ElseIf Sheets("Jan").Range("D" & NextRow) = "A0" & Sheets("Jan").Range("b" & NextRow) <= 30 Then
Sheets("Jan").Range("E" & NextRow) = "5"
ElseIf Sheets("Jan").Range("D" & NextRow) = "A0" & Sheets("Jan").Range("b" & NextRow) >= 31 Then
Sheets("Jan").Range("E" & NextRow) = "8"
ElseIf Sheets("Jan").Range("D" & NextRow) = "A0" & Sheets("Jan").Range("b" & NextRow) >= 61 Then
Sheets("Jan").Range("E" & NextRow) = "10"
       
ElseIf Sheets("Jan").Range("D" & NextRow) = "A2" & Sheets("Jan").Range("b" & NextRow) <= 30 Then
Sheets("Jan").Range("E" & NextRow) = "1.25"
ElseIf Sheets("Jan").Range("D" & NextRow) = "A2" & Sheets("Jan").Range("b" & NextRow) >= 31 Then
Sheets("Jan").Range("E" & NextRow) = "2"
ElseIf Sheets("Jan").Range("D" & NextRow) = "A2" & Sheets("Jan").Range("b" & NextRow) >= 61 Then
Sheets("Jan").Range("E" & NextRow) = "2.5"
       
ElseIf Sheets("Jan").Range("D" & NextRow) = "A3" Then
Sheets("Jan").Range("E" & NextRow) = "0.5"

       
End If
         
              
            End If
        Next Bcell
End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
the part in red should be the word "And" not an ampersand
Code:
If Sheets("Jan").Range("D" & NextRow) = "A1"[COLOR=#ff0000] &[/COLOR] Sheets("Jan").Range("B" & NextRow) <= 30 Then
You will need to change this for all your If statements
 
Upvote 0
the part in red should be the word "And" not an ampersand
Code:
If Sheets("Jan").Range("D" & NextRow) = "A1"[COLOR=#ff0000] &[/COLOR] Sheets("Jan").Range("B" & NextRow) <= 30 Then
You will need to change this for all your If statements

Doh.

Thanks very much. Worked straight away.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top