Help with "" and & again...

[JARichard74]

Get error 1004 Application defined error

.Range(.Cells(startCell, 6), .Cells(startCell, 6)).Formula = "=(COUNTIFS(B" & startRow & ":B" & endRow & ", ""<""& " & Hrngel & ",B" & startRow & ":B" & endRow & ", "">""& " & Hrngeh & ", ""<""& B" & startCell & ")+1)"

Where
Hrngel = WorksheetFunction.Average(Range("B" & startRow & ":B" & endRow)) - WorksheetFunction.StDev(Range("B" & startRow & ":B" & endRow))
Hrngeh = WorksheetFunction.Average(Range("B" & startRow & ":B" & endRow)) + WorksheetFunction.StDev(Range("B" & startRow & ":B" & endRow))
Both Dim As Double

 

[jasonb75]

You're either missing a range or have too many criteria for the formula.
I would need to see an example of the expected worksheet formula in order to correct it.

 

[JARichard74]

You're either missing a range or have too many criteria for the formula.
I would need to see an example of the expected worksheet formula in order to correct it.

=COUNTIFS($A$6:$A$8,"<"&Hrngeh,$A$6:$A$8,">"&Hrngel,$A$6:$A$8,"<"&A6)
Hrngeh = E2 which = AVERAGE(A6:A8)+STDEV(A6:A8)
Hrngel = D2 which = AVERAGE(A6:A8)-STDEV(A6:A8)

 

[jasonb75]

The third $A$6:$A$8 was missing, this should work

.Range(.Cells(startCell, 6), .Cells(startCell, 6)).Formula = "=(COUNTIFS(B" & startRow & ":B" & endRow & ", ""<""& " & Hrngel & ",B" & startRow & ":B" & endRow & ", "">""& " & Hrngeh & ",B" & startRow & ":B" & endRow & ", ""<""& B" & startCell & ")+1)"

 

[JARichard74]

The third $A$6:$A$8 was missing, this should work

.Range(.Cells(startCell, 6), .Cells(startCell, 6)).Formula = "=(COUNTIFS(B" & startRow & ":B" & endRow & ", ""<""& " & Hrngel & ",B" & startRow & ":B" & endRow & ", "">""& " & Hrngeh & ",B" & startRow & ":B" & endRow & ", ""<""& B" & startCell & ")+1)"

Thank you!