VBA application-defined or object-defined error

[JARichard74]

Get the error on this code on the first .Range line

ElseIf scle = "MDB-H" Then
startRow = k * z + 4 - z
endRow = k * z + 2
startCell = k * z + j + 3 - z
Hrngel = WorksheetFunction.Average(Range("B" & startRow & ":B" & endRow)) - WorksheetFunction.StDevP(Range("B" & startRow & ":B" & endRow))
Hrngeh = WorksheetFunction.Average(Range("B" & startRow & ":B" & endRow)) + WorksheetFunction.StDevP(Range("B" & startRow & ":B" & endRow))
OOrnge = WorksheetFunction.CountIf(Range("J" & startRow & ":J" & endRow), "No") - 1
    [COLOR=rgb(184, 49, 47)].Range(.Cells(startCell, 10), .Cells(startCell, 10)).Formula = "=IF(OR(B" & startCell & " < Hrngel,B" & startCell & " > Hrngeh,""No"",""Yes"")"[/COLOR]
    .Range(.Cells(startCell, 6), .Cells(startCell, 6)).Formula = "=IF(OR(B" & startCell & " < Hrngel,B" & startCell & " > Hrngeh,z - 1,RANK.EQ(B" & startCell & ",B" & k * z + 4 - z & ":B" & (k * z) + 2 & ",1) - OOrnge))"
    .Range(.Cells(startCell, 3), .Cells(startCell, 3)).Formula = "=IF(OR(B" & startCell & " < rngel,B" & startCell & " > rngeh),0,VLOOKUP(B" & startCell & "/B" & (k * z + 3 - z) & "*100,Data!$G$3:$I$13,3,TRUE))"
    .Range(.Cells(startCell, 5), .Cells(startCell, 5)).Formula = "=IF(ISBLANK(B" & startCell & "),"""",IF(B" & startCell & " = ""Locked"","""",C" & startCell & " * D" & startCell & "/10))"
    .Range("B" & startCell).NumberFormat = "$#,##0.00"

 

[GWteB]

A parenthesis is missing in your formula:

.Range(.Cells(startCell, 10), .Cells(startCell, 10)).Formula = "=IF(OR(B" & startCell & " < Hrngel,B" & startCell & " > Hrngeh,""No"",""Yes""))"
                                                                                                                                              /\

 

[JARichard74]

A parenthesis is missing in your formula:

.Range(.Cells(startCell, 10), .Cells(startCell, 10)).Formula = "=IF(OR(B" & startCell & " < Hrngel,B" & startCell & " > Hrngeh,""No"",""Yes""))"
                                                                                                                                              /\

Thanks for pointing in the right direction. Actually the OR bracket was missing. The code works but places something not workable which indicates I am not using the variables properly. This is the formula that gets put in the cells =IF(OR($B24 < 2,$B24 <@ Mrnge - 2,$B24 <@ Mrnge + 2),"No","Yes")

 

[Fluff]

There is no way that line of code can insert the formula you have shown.
What are Hrngel & Hrngeh?

 

[JARichard74]

There is no way that line of code can insert the formula you have shown.
What are Hrngel & Hrngeh?

Sorry, wrong cell. This is what it returns =IF(OR(B9 <@ Hrngel,B9 >@ Hrngeh),"No","Yes"). Hrngel and Hrngeh are declared as Long.

Hrngel = WorksheetFunction.Average(Range("B" & startRow & ":B" & endRow)) - WorksheetFunction.StDevP(Range("B" & startRow & ":B" & endRow))
Hrngeh = WorksheetFunction.Average(Range("B" & startRow & ":B" & endRow)) + WorksheetFunction.StDevP(Range("B" & startRow & ":B" & endRow))

 

[GWteB]

Actually the OR bracket was missing.

You are correct, I wasn't. This again shows how quickly one overlooks something.

The code works but places something not workable which indicates I am not using the variables properly.

It's all about the quotation marks.
Replace this

.Formula = "=IF(OR(B" & startCell & " < Hrngel,B" & startCell & " > Hrngeh,""No"",""Yes"")"

by this (and change the other lines of code accordingly...).

.Formula = "=IF(OR(B" & startCell & " < " & Hrngel & ", B" & startCell & " > " & Hrngeh & "), " & """No""" & ", " & """Yes""" & ")"

 

[JARichard74]

You are correct, I wasn't. This again shows how quickly one overlooks something.


It's all about the quotation marks.
Replace this

.Formula = "=IF(OR(B" & startCell & " < Hrngel,B" & startCell & " > Hrngeh,""No"",""Yes"")"

by this (and change the other lines of code accordingly...).

.Formula = "=IF(OR(B" & startCell & " < " & Hrngel & ", B" & startCell & " > " & Hrngeh & "), " & """No""" & ", " & """Yes""" & ")"

Thanks. Marked as solution