CODEWITHSUNDEEP
vbaexcelfor-loopif-statement
Tomasz Przemski
Tomasz Przemski

Reputation: 1127

If statement inside for loop in VBA does not work

I have such a sheet:

G                M              N           O
Warrant/Rights                              08-06-2020
NA          
Share                           1   
Share           
Share                           1   
Share            4,5                        15-02-2026
Share                                       06-01-2026
Share            4                          15-03-2023
Share                                       30-09-2027

Then I try to compare the G column by type Share with the column O by date. In this way, if in the column O is the percentage of not enough entered dates, the program is to clear all cells from M to O after the type Share. I have something like this:

Sub aaa()
    x1 = Application.WorksheetFunction.CountIfs(Range("G2:G" & rows.Count), "Share")
    y1 = Application.WorksheetFunction.CountIfs(Range("G2:G" & rows.Count), "Share", Range("O2:O" & rows.Count), "")
    If x1 > 0 Then
        z1 = (y1 / x1) * 100
        Dim LR24 As Long, i24 As Long
        LR24 = Range("G" & rows.Count).End(xlUp).Row
        For i24 = LR24 To 1 Step -1
            If (Range("G" & i24).Value = "Share" And z1 < 90) Then Range("M" & i24 & ":O" & i24).Clear
        Next i24
    End If
End Sub

After removing the condition If x1> 0 Then everything works, but I have to put it in order to protect myself from division by 0. Do you have an idea why it does not work?

Upvotes: 1

Views: 212

Answers (1)

ashleedawg
ashleedawg

Reputation: 21619

You could remove your If...End If statement and instead replace your z1=... line with:

z1 = IIf(x1 = "", 0, (y1 / x1) * 100)

It's untested so I don't know how it will affect the rest of your code, but you won't get the Div/0 error on that line.

Instead of an If, it uses the Iif function (short for Inline If or Immediate If, depending on who you ask).

There's no reason you couldn't also use a standard If..Then statement in the same way, located only around the one line instead of preventing execution of a section of code.

More Information:

Upvotes: 1

Related Questions