Manual enter formula 'conflicts' with VBA created formula

[BrianJN1]

I have a rather complicated table of formulae which could be easily corrupted by manual manipulation (I'm aware of password protect but that is not my concern). I have set about rewriting all of the formulae in that table in Visual Basic.

Now in one particular column the formulae are of the format:
=IF(C$2<Glob_Data!L11+1,COUNTIF($A:$A,"<"&D5+1),"") and if there is no data present the cell reports "0".

COUNTIF($A:$A, "<" & D5+1) reports the value held in D5 plus 1.

Writing that under VBA I have:
Cells(5,6).formula="=IF(C$2<Glob_Data!L11+1,COUNTIF($A:$A, "<" & D5+1),"""")" and so the only difference in the formula is the quotes at the end of that under VBA. There is are no compilation errors report but when I execute that formula the cell holds the value FALSE, no formula is present. That is even so if Column A does hold values.

Clearly there is something wrong with my syntax. I'd appreciate some guidance to rectify that.
I don't see any point in offering that table as it would be too complicated to explain in writing what is going on.

 

[Michael M]

Hi Brian
Can you repost, and put a space before and after any "<" and ">" signs please ?
The forum sees these as HTML tags and removes the rest of the formula.

 

[BrianJN1]

I have a rather complicated table of formulae which could be easily corrupted by manual manipulation (I'm aware of password protect but that is not my concern). I have set about rewriting all of the formulae in that table in Visual Basic.

Now in one particular column the formulae are of the format:
=IF(C$2<glob_data!l11+1,countif($a:$a,"<"&d5+1),"") and="" if="" there="" is="" no="" data="" present="" the="" cell="" reports="" "0".

COUNTIF($A:$A, "<" & D5+1) reports the value held in D5 plus 1.

Writing that under VBA I have:
Cells(5,6).formula="=IF(C$2<glob_data!l11+1,countif($a:$a, "<"="" &="" d5+1),"""")"="" and="" so="" the="" only="" difference="" in="" formula="" is="" quotes="" at="" end="" of="" that="" under="" vba.="" there="" are="" no="" compilation="" errors="" report="" but="" when="" i="" execute="" cell="" holds="" value="" false,="" present.="" even="" if="" column="" a="" does="" hold="" values.

Clearly there is something wrong with my syntax. I'd appreciate some guidance to rectify that.
I don't see any point in offering that table as it would be too complicated to explain in writing what is going on.

Michael, I didn't check back upon this so missed the deletion. The manual formula to which I referred reads as:
=IF(C$2 < Glob_Data!L11+1,COUNTIF($A:$A," < " &D5+1),"").

In case there is another issue I'm going to put "**" where the "<" should go:
=IF(C$2 ** Glob_Data!L11+1,COUNTIF($A:$A, "**" &D5+1),"") The first "<" is NOT to be in quotes but the second is.
Let's see how this entry compiles.

</glob_data!l11+1,countif($a:$a,></glob_data!l11+1,countif($a:$a,"<"&d5+1),"")>

 

[BrianJN1]

Good, formula is correct.

 

[Michael M]

Maybe this Brian

Cells(5, 6).Formula = "=IF(C$2 < Glob_Data!L11+1,COUNTIF($A:$A,""<"" &D5+1),"""")"

 

[BrianJN1]

The only thing that I see that you have done is to put the second "<" in double quotes.
I've just done that but on execution I have a run-time error - Application-defined or object error.
( I actually isolate that formula to its own macro for convenience).

 

[Michael M]

The second " < " AND the final "blank" are both requiring double quotes.
The formual I attached works fine for me via VBA
2 Things to check
Make sure Glob_Data sheet is not protected
and
run the code when the sheet that requires the formula is the active sheet

 

[BrianJN1]

Ah, Michael. I had an "&" leftover from and edit and that was creating my error message. Yes, I had both double quotes as you mentioned so now my problem is solved. Thanks.

 

[Michael M]

Glad to help ....

Not a good day for golf though !!!

 

[BrianJN1]

Spot on! I'm warm, programming and ......
see you around.