Help with VBA formula

[JARichard74]

The result of the formula should be something like this:
=IF(OR(A19<2,A19<$B$18-2,A19>$B$18+2),COUNTA($A$19:$A$24),COUNTIFS($A$19:$A$23,">="&2,$A$19:$A$23,"<="&$B$18+2,$A$19:$A$23,"<"&A19)+1). Formula used to do a RANK IF
The error is most likely a missing " and/or & starting with COUNTA part. The Condition should be ok as I am already using the same condition in another formula

"=IF(Condition,COUNTA(B" & startRow & ":B" & endRow & "),COUNTIFS(B" & startRow & ":B" & endRow & ", >= 2,B" & startRow & ":B" & endRow & ", <= " & Mrnge & " + 2,B" & startRow & ":B" & endRow & ", < B" & startRow & ")+1)"

 

[Fluff]

I removed the IF statement in the new formula

You can't do that, because it's no longer a legitimate formula.

 

[JARichard74]

You can't do that, because it's no longer a legitimate formula.

Here is the full original formula with the fixes you submitted for the COUNTIFS statement. I never had a variable named Condition. I was just trying to shorten the code

.Range(.Cells(startCell, 6), .Cells(startCell, 6)).Formula = "=IF(B" & startCell & " = ""Locked"","""",IF(OR($B" & startCell & " < 2,$B" & startCell & " < " & Mrnge & " - 2,$B" & startCell & " > " & Mrnge & " + 2),COUNTA(B" & startRow & ":B" & endRow & "),COUNTIFS(B" & startRow & ":B" & endRow & ", "">= 2"",B" & startRow & ":B" & endRow & ", ""<=""& " & Mrnge & " + 2,B" & startRow & ":B" & endRow & ", ""<""& B" & startCell & ")+1)"

 

[JARichard74]

Here is the full original formula with the fixes you submitted for the COUNTIFS statement. I never had a variable named Condition. I was just trying to shorten the code

.Range(.Cells(startCell, 6), .Cells(startCell, 6)).Formula = "=IF(B" & startCell & " = ""Locked"","""",IF(OR($B" & startCell & " < 2,$B" & startCell & " < " & Mrnge & " - 2,$B" & startCell & " > " & Mrnge & " + 2),COUNTA(B" & startRow & ":B" & endRow & "),COUNTIFS(B" & startRow & ":B" & endRow & ", "">= 2"",B" & startRow & ":B" & endRow & ", ""<=""& " & Mrnge & " + 2,B" & startRow & ":B" & endRow & ", ""<""& B" & startCell & ")+1)"

It works! I was missing a bracket....Thank you for your patience and assistance. Marked as solution

 

[Fluff]

Glad you sorted it & thanks for the feedback.