How to use If & CountIf on VBA

[ExcelPupper]

I am trying to put IF & CountIF on the same formula on the code below but I get syntax error. What could be the problem?

Sub CntNoEntry ()
Dim lr as long

Sheets(6).Range("A1").Formula = "=IF((COUNTIF("G8:G" & lr,"*No Entry*"))>0,"Provide Data","")"

End Sub

 

[Peter_SSs]

What could be the problem?

Quite a few things

1. You have " before G8 which should not be there.
2. After using lr in the formula you need an ampersand and to re-start the string with a quote mark.
3. All quote marks the are to end up in the final formula need to be doubled-up in the code.

Try

Sheets(6).Range("A1").Formula = "=IF((COUNTIF(G8:G" & lr & ",""*No Entry*""))>0,""Provide Data"","""")"

 

[Peter_SSs]

BTW, you don't actually need the >0 and there is an extra set of parentheses that is not needed.

Sheets(6).Range("A1").Formula = "=IF(COUNTIF(G8:G" & lr & ",""*No Entry*""),""Provide Data"","""")"

 

[ExcelPupper]

BTW, you don't actually need the >0 and there is an extra set of parentheses that is not needed.

Sheets(6).Range("A1").Formula = "=IF(COUNTIF(G8:G" & lr & ",""*No Entry*""),""Provide Data"","""")"

Thanks for this. Most of the time, those "" placing are the reason for my errors.
Btw, I add the >0 because if the count of No Entry on G column is greater than zero, it should prompt the user by showing provide data on cell A2.

 

[Peter_SSs]

Thanks for this. Most of the time, those "" placing are the reason for my errors.

That is common for a lot of people.

Btw, I add the >0 because if the count of No Entry on G column is greater than zero, it should prompt the user ..

It will still do that with or without the >0
In column A below you will see that one formula has the >0 & parentheses as you had them and one has them removed but both formulas return the same result.

20 07 21.xlsm
	A	B	C	D	E	F	G
1	Provide Data
2	Provide Data
3
4
5							There is no entry here
6
7							No entry here either
8
Sheet6

Cell Formulas
Range	Formula
A1	A1	=IF((COUNTIF(G4:G8,"*No Entry*"))>0,"Provide Data","")
A2	A2	=IF(COUNTIF(G4:G8,"*No Entry*"),"Provide Data","")

.. and if I remove or replace the 'No Entry' values the formulas still return identical results to each other

20 07 21.xlsm
	A	B	C	D	E	F	G
1
2
3
4
5							Entry
6
7							Data
8
Sheet6

Cell Formulas
Range	Formula
A1	A1	=IF((COUNTIF(G4:G8,"*No Entry*"))>0,"Provide Data","")
A2	A2	=IF(COUNTIF(G4:G8,"*No Entry*"),"Provide Data","")

 

[ExcelPupper]

That is common for a lot of people.


It will still do that with or without the >0
In column A below you will see that one formula has the >0 & parentheses as you had them and one has them removed but both formulas return the same result.

20 07 21.xlsm
	A	B	C	D	E	F	G
1	Provide Data
2	Provide Data
3
4
5							There is no entry here
6
7							No entry here either
8
Sheet6

Cell Formulas
Range	Formula
A1	A1	=IF((COUNTIF(G4:G8,"*No Entry*"))>0,"Provide Data","")
A2	A2	=IF(COUNTIF(G4:G8,"*No Entry*"),"Provide Data","")

.. and if I remove or replace the 'No Entry' values the formulas still return identical results to each other

20 07 21.xlsm
	A	B	C	D	E	F	G
1
2
3
4
5							Entry
6
7							Data
8
Sheet6

Cell Formulas
Range	Formula
A1	A1	=IF((COUNTIF(G4:G8,"*No Entry*"))>0,"Provide Data","")
A2	A2	=IF(COUNTIF(G4:G8,"*No Entry*"),"Provide Data","")

Ohh nice. Does it mean if the CountIf formula returns a value greater than 0, it is regarded as TRUE? IS my understanding correct?

 

[Peter_SSs]

For an IF function test, zero counts as FALSE, any other numerical value (positive or negative) counts as TRUE