# Help to modify 1 line of VBA code IF/Then



## ItalianPlatinum (Dec 29, 2022)

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 (Dec 29, 2022)

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 (Dec 30, 2022)

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
```


----------

