CODEWITHSUNDEEP
vbaexcel
yashika vaish
yashika vaish

Reputation: 201

Vba Issue in If Else Condition

Set ws1 = wb.Worksheets("MacroGeneratedReport")
LastRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1
If ws1.Cells(i, "I").Value = "A" Or ws1.Cells(i, "I").Value = "B" Or 
 ws1.Cells(i, "I").Value = "C" Or ws1.Cells(i, "I").Value = "D" Or 
 ws1.Cells(i, "I").Value = "E" Or ws1.Cells(i, "I").Value = "F" Or 
 ws1.Cells(i, "I").Value = "Y" Then
 ws1.Cells(i, "AO").Value = ws1.Cells(i, "Y").Value
Else
 **ws1.Cells(i, "AO").Value = ws1.Cells(i, "AP").Value * ws1.Cells(i, "W").Value**
End If
Next i

In the above code, The bold highlighted "Else condition" gives runtime error, while the code is giving correct output and correct value according to the condition. Please suggest the necessary changes.

Upvotes: 1

Views: 74

Answers (2)

iDevlop
iDevlop

Reputation: 25252

Not an answer, but you could simplify your code, without Case ;-)

If UCase(ws1.Cells(i, "I").Value) Like "[A-FY]" Then

(The UCase is not required if you have Option Compare Text)

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33672

Your code is "Screaming" to use Select Case instead of your multiple Ors.

Code

Set ws1 = wb.Worksheets("MacroGeneratedReport")
With ws1
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = LastRow To 1 Step -1
        Select Case .Cells(i, "I").Value
            Case "A", "B", "C", "D", "E", "F", "Y"
                .Cells(i, "AO").Value = .Cells(i, "Y").Value

            Case Else
                ' check that both values are numeric
                If IsNumeric(.Cells(i, "AP").Value) And IsNumeric(.Cells(i, "W").Value) Then
                    .Cells(i, "AO").Value = .Cells(i, "AP").Value * .Cells(i, "W").Value
                End If            
        End Select
    Next i
End With

Upvotes: 4

Related Questions