Help to modify 1 line of VBA code IF/Then

[ItalianPlatinum]

Hello - I have a line of code that will check if the row counts of two sheets is less than or equal to a number. But i need to modify the 2nd part to not check the row count but to check if any value = No in column M, data starts on row 3.

'check if any current and prior day records exist and stop VBA if condition is met
If WsSP.Range("A" & rows.count).End(xlUp).row <= 3 And WsSP1.Range("A" & rows.count).End(xlUp).row <= 3 Then

'other code like format, formulas etc....

MsgBox ("No Current Day or Prior Day Dates")
Else

'other code like format, formulas etc....

End If
End Sub

So SP1 needs to be amended per the above

 

[DanteAmor]

Try this:

  Dim n As Long
  n = WorksheetFunction.CountIf(WsSP1.Range("M3", WsSP1.Range("M" & Rows.Count).End(xlUp)), "No")
  'check if any current and prior day records exist and stop VBA if condition is met
  If WsSP.Range("A" & Rows.Count).End(xlUp).Row <= 3 And n = 0 Then
    'other code like format, formulas etc....
    MsgBox ("No Current Day or Prior Day Dates")
  Else
    'other code like format, formulas etc....
  End If

 

[ItalianPlatinum]

Thanks Dante - I will give this a try. I ended up just creating a formula putting it in another cell and referencing it. But i think both accomplish what I am looking for

With WsSP1
.Range("M1").Formula = "=IF(COUNTIF($M$3:$M$10000,""NO"")>0,""NO"",""Yes"")"
end with
If WsSP1.Range("M1") = "YES" Then