Help with VBA formula

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
114
Office Version
  1. 365
Platform
  1. Windows
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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 & "
 
Upvote 0
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)
 
Upvote 0
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)"
 
Upvote 0
Solution
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
 
Upvote 0
In that case can you please post your entire code.
 
Upvote 0
In that case can you please post your entire code.
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
 
Upvote 0
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
 
Upvote 0
I'm confused, the formula you asked about isn't in that code?
Also there is no variable called "Condition"
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top