Multiple If Statement

sg2209

Board Regular
Joined
Oct 27, 2017
Messages
89
Office Version
  1. 2016
hi Friends ,

i am just trying to write the VBA Code for multiple if statement even i have recorded the MACRO however i am not getting the results what i needed , below is the formula in excel

IF(AD2<=0,"Negative",IF(AD2<67,"$1 - $67",IF(AD2<100,"$67 - $100",IF(AD2<200,"$100 - $200",IF(AD2<300,"$200 - $300",IF(AD2<500,"$300 - $500",IF(AD2<1000,"$500-$1000",">=1000")))))))
Now i need if this criteria meets the results should be populated in Column AE until the AD has its last row , please help me in achieving this .


Appreciate all your help
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
Re: Mutliple If Statement

Hi,

try something like this:
Code:
Sub Mutliple_If_Statement()

Dim lRow, I, E_cell As Long
    
    lRow = Cells(Rows.Count, 30).End(xlUp).Row              'Find the last non-blank cell in column AD(30)
        
    For I = 2 To lRow                                       'Loop through rows till last Row reached
        E_cell = Cells(I, 30).Value                         'Insert cell value of Column AD into an evaluation Variable
                
        Select Case E_cell                                  'use evaluation variable to determine correct value in Column AE (31)
            Case Empty
                Cells(I, 31).Value = ""
            Case Is <= 0
                Cells(I, 31).Value = "Negative"
            Case Is < 67
                Cells(I, 31).Value = "$1 - $67"
            Case Is < 100
                Cells(I, 31).Value = "$67 - $100"
            Case Is < 200
                Cells(I, 31).Value = "$100 - $200"
            Case Is < 300
                Cells(I, 31).Value = "$200 - $300"
            Case Is < 500
                Cells(I, 31).Value = "$300 - $500"
            Case Else
                Cells(I, 31).Value = ">= $1000"
        End Select
    Next
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,104
Messages
5,599,750
Members
414,333
Latest member
willfrederick

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
Top