Multiple If Statement

[sg2209]

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

 

[jorismoerings]

Re: Mutliple If Statement

Hi,

try something like this:

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