Help with VBA formula

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
102
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

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
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
What is "Condition"?
 

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
102
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 16, 2019
Messages
102
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)"
 
Solution

JARichard74

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case can you please post your entire code.
 

JARichard74

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

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
102
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
I'm confused, the formula you asked about isn't in that code?
Also there is no variable called "Condition"
 
Learn Excel from Bill Jelen

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

Forum statistics

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