Loop using if and

[Manny]

Hello Chandoo users,
My I please have your help, for I am new at VBA?
I need to create a macro-enabled workbook that needs to look at column C(starting at cell 63) and check if it is greater than zero, and if it is , then to also check the range (s:z) on that same row and if any of the cells in the range s:z are blank, then I need to have a message to say "one or more of the cells are blank". I need need this to loop so that it can keep looking down the rows from c3:c1002 respectively.

I was only able to do it for one line:

Sub Test_Yellow_fields()

If Sheets("Order").Range("C3").Value > 0 And WorksheetFunction.CountA(Range("S3:Z3")) = 0 Then
MsgBox "Missing Data in yellow at line level for the corresponding line"
Else
MsgBox "No missing data"

End If

End Sub

I would greatly appreciate your help on this.

Thanks,
Manny

 

[YasserKhalil]

Not tested

Sub Test_Yellow_fields()
    Dim I As Long

    For I = 3 To 1002
        If Sheets("Order").Range("C" & I).Value > 0 And WorksheetFunction.CountA(Range("S" & I & ":Z" & I)) = 0 Then
            MsgBox "Missing Data in yellow at line level for the corresponding line"
        Else
            MsgBox "No missing data"

        End If
    Next I
End Sub

 

[Marc L]

Hi !

Try this :​

Sub Demo()
            Const F = "IF(S#:Z#="""",ADDRESS(#,COLUMN(S#:Z#),4))"
    With Sheet1
        With .UsedRange.Rows:  A$ = .Item("3:" & .Count).Columns(4).Address:  End With
        For Each V In Filter(.Evaluate("TRANSPOSE(IF(" & A & ">"""",ROW(" & A & ")))"), False, False)
            VA = Filter(.Evaluate(Replace(F, "#", V)), False, False)
            If UBound(VA) > -1 Then
                .Activate
                .Range(VA(0)).Select
                MsgBox "Missing data in " & Join(VA, " & "), vbExclamation, "  Control !"
                Exit For
            End If
        Next
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Manny]

Hi Khalid,
Thanks for your help. I will try this next time.

Best regards,
Manny

 

[Manny]

Not tested

Sub Test_Yellow_fields()
    Dim I As Long

    For I = 3 To 1002
        If Sheets("Order").Range("C" & I).Value > 0 And WorksheetFunction.CountA(Range("S" & I & ":Z" & I)) = 0 Then
            MsgBox "Missing Data in yellow at line level for the corresponding line"
        Else
            MsgBox "No missing data"

        End If
    Next I
End Sub

Hi Yasser, thanks for your help.
I tried your code, but if any of the cells is empty in the range s:z, it should give me the MsgBox, and it does not.

Thanks,
Manny