jeudi 21 juin 2018

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?

Aucun commentaire:

Enregistrer un commentaire