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.

Forum statistics

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.    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