# Help with VBA formula

#### JARichard74

##### Board Regular
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
VBA Code:
``"=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)"``

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Fluff

##### MrExcel MVP, Moderator
What is "Condition"?

#### JARichard74

##### Board Regular
What is "Condition"?
It's the IF condition which is used in another formula and works OK
VBA Code:
``B" & startCell & " = ""Locked"","""",IF(OR(\$B" & startCell & " < 2,\$B" & startCell & " < " & Mrnge & " - 2,\$B" & startCell & " > " & Mrnge & " + 2)," & z - 1 & "``

#### JARichard74

##### Board Regular
It's the IF condition which is used in another formula and works OK
VBA Code:
``B" & startCell & " = ""Locked"","""",IF(OR(\$B" & startCell & " < 2,\$B" & startCell & " < " & Mrnge & " - 2,\$B" & startCell & " > " & Mrnge & " + 2)," & z - 1 & "``
Sorry the Condition should be
VBA Code:
``B" & startCell & " = ""Locked"","""",IF(OR(\$B" & startCell & " < 2,\$B" & startCell & " < " & Mrnge & " - 2,\$B" & startCell & " > " & Mrnge & " + 2)``

#### Fluff

##### MrExcel MVP, Moderator

In that case try
VBA Code:
``"=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)"``

#### JARichard74

##### Board Regular
In that case try
VBA Code:
``"=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)"``
Still get error 1004 application defined error

#### JARichard74

##### Board Regular
The code was working fine until I introduced this new formula to replace the one just under it. Formula in Col 6 is causing the trouble
VBA Code:
``````Dim Hrngel As Double, Hrngeh As Double
Dim Mrnge As Long, OOrnge As Long
Dim startRow As Long, endRow As Long, startCell As Long
With Worksheets("Price")
For k = 1 To y 'y = Number of Price criteria
For j = 1 To z - 1 'z-1 = Number of Proponents
startRow = k * z + 4 - z
endRow = k * z + 2
startCell = k * z + j + 3 - z
ThisWorkbook.Worksheets("Price").Range("B" & k * z + 3 + j - z).NumberFormat = "0.00"
Hrngel = FormatNumber(WorksheetFunction.Average(Range("B" & startRow & ":B" & endRow)) - WorksheetFunction.StDev(Range("B" & startRow & ":B" & endRow)), 2)
Hrngeh = FormatNumber(WorksheetFunction.Average(Range("B" & startRow & ":B" & endRow)) + WorksheetFunction.StDev(Range("B" & startRow & ":B" & endRow)), 2)
Mrnge = WorksheetFunction.Average(Range("B" & k * z + 4 - z & ":B" & (k * z) + 2))
OOrnge = (WorksheetFunction.CountIf(Range("J" & k * z + 4 - z & ":J" & (k * z) + 2), "No") - 1)
.Range(.Cells(startCell, 10), .Cells(startCell, 10)).Formula = "=IF(OR(\$B" & startCell & " < 2,\$B" & startCell & " < " & Mrnge & " - 2,\$B" & startCell & " > " & Mrnge & " + 2),""No"",""Yes"")"
.Range(.Cells(startCell, 6), .Cells(startCell, 6)).Formula = "=COUNTA(B" & startRow & ":B" & endRow & "),COUNTIFS(B" & startRow & ":B" & endRow & ", "">= 2"",B" & startRow & ":B" & endRow & ", ""<=""& " & Mrnge & " + 2,B" & startRow & ":B" & endRow & ", ""<""& B" & startCell & ")+1)"
'.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)," & z - 1 & ",RANK.EQ(\$B" & startCell & ",B" & startRow & ":B" & (k * z) + 2 & ",1) - " & OOrnge & "))"
.Range(.Cells(startCell, 5), .Cells(startCell, 5)).Formula = "=IF(ISBLANK(\$B" & startCell & "),"""",IF(\$B" & startCell & " = ""Locked"","""",\$C" & startCell & " * \$D" & startCell & "/10))"
.Range(.Cells(startCell, 3), .Cells(startCell, 3)).Formula = "=IF(B" & startCell & " = ""Locked"","""",IF(OR(\$B" & startCell & " < 2,\$B" & startCell & " < " & Mrnge & " - 2,\$B" & startCell & " > " & Mrnge & " + 2),0,12 - 2 * \$F" & startCell & "))"
ThisWorkbook.Worksheets("Price").Range("B" & k * z + 3 - z).FormulaArray = "=MIN(IF((B" & startRow & ":B" & endRow & " < " & Hrngeh & ") * (B" & startRow & ":B" & endRow & " > " & Hrngel & "),B" & startRow & ":B" & endRow & "))"
'ThisWorkbook.Worksheets("Price").Range("B" & k * z + 3 - z).Formula = "=MIN(B" & startRow & ":B" & endRow & ")"
Next j
Next k
End With``````

#### JARichard74

##### Board Regular
The code was working fine until I introduced this new formula to replace the one just under it. Formula in Col 6 is causing the trouble
VBA Code:
``````Dim Hrngel As Double, Hrngeh As Double
Dim Mrnge As Long, OOrnge As Long
Dim startRow As Long, endRow As Long, startCell As Long
With Worksheets("Price")
For k = 1 To y 'y = Number of Price criteria
For j = 1 To z - 1 'z-1 = Number of Proponents
startRow = k * z + 4 - z
endRow = k * z + 2
startCell = k * z + j + 3 - z
ThisWorkbook.Worksheets("Price").Range("B" & k * z + 3 + j - z).NumberFormat = "0.00"
Hrngel = FormatNumber(WorksheetFunction.Average(Range("B" & startRow & ":B" & endRow)) - WorksheetFunction.StDev(Range("B" & startRow & ":B" & endRow)), 2)
Hrngeh = FormatNumber(WorksheetFunction.Average(Range("B" & startRow & ":B" & endRow)) + WorksheetFunction.StDev(Range("B" & startRow & ":B" & endRow)), 2)
Mrnge = WorksheetFunction.Average(Range("B" & k * z + 4 - z & ":B" & (k * z) + 2))
OOrnge = (WorksheetFunction.CountIf(Range("J" & k * z + 4 - z & ":J" & (k * z) + 2), "No") - 1)
.Range(.Cells(startCell, 10), .Cells(startCell, 10)).Formula = "=IF(OR(\$B" & startCell & " < 2,\$B" & startCell & " < " & Mrnge & " - 2,\$B" & startCell & " > " & Mrnge & " + 2),""No"",""Yes"")"
.Range(.Cells(startCell, 6), .Cells(startCell, 6)).Formula = "=COUNTA(B" & startRow & ":B" & endRow & "),COUNTIFS(B" & startRow & ":B" & endRow & ", "">= 2"",B" & startRow & ":B" & endRow & ", ""<=""& " & Mrnge & " + 2,B" & startRow & ":B" & endRow & ", ""<""& B" & startCell & ")+1)"
'.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)," & z - 1 & ",RANK.EQ(\$B" & startCell & ",B" & startRow & ":B" & (k * z) + 2 & ",1) - " & OOrnge & "))"
.Range(.Cells(startCell, 5), .Cells(startCell, 5)).Formula = "=IF(ISBLANK(\$B" & startCell & "),"""",IF(\$B" & startCell & " = ""Locked"","""",\$C" & startCell & " * \$D" & startCell & "/10))"
.Range(.Cells(startCell, 3), .Cells(startCell, 3)).Formula = "=IF(B" & startCell & " = ""Locked"","""",IF(OR(\$B" & startCell & " < 2,\$B" & startCell & " < " & Mrnge & " - 2,\$B" & startCell & " > " & Mrnge & " + 2),0,12 - 2 * \$F" & startCell & "))"
ThisWorkbook.Worksheets("Price").Range("B" & k * z + 3 - z).FormulaArray = "=MIN(IF((B" & startRow & ":B" & endRow & " < " & Hrngeh & ") * (B" & startRow & ":B" & endRow & " > " & Hrngel & "),B" & startRow & ":B" & endRow & "))"
'ThisWorkbook.Worksheets("Price").Range("B" & k * z + 3 - z).Formula = "=MIN(B" & startRow & ":B" & endRow & ")"
Next j
Next k
End With``````
I removed the IF statement in the new formula for now until I fix the error

#### Fluff

##### MrExcel MVP, Moderator
Also there is no variable called "Condition"

Replies
8
Views
534
Replies
1
Views
91
Replies
2
Views
74
Replies
5
Views
189
Replies
5
Views
452

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,589
Messages
5,765,308
Members
425,272
Latest member
Umba

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back