AutoFill If Formulas - Help

[CoraG]

To whom it may concern;

I am getting an error of Run-tim error '1004 Application-defined or object-defined error with the following code. Can you tell me why? Are we not able to do autofill on If formulas?
Thanks,
Cora

Sub FillFormulas2s()

Dim ws As Worksheet
Set ws = Sheets("OCT ADJ")

Dim FormRegion As Range
Set FormRegion = Sheets("OCT ADJ").Range("V8:Y8", Range("V8:Y8").End(xlDown))
Dim erow As Long
erow = Sheets("OCT ADJ").Cells(Rows.Count, "A").End(xlUp).Row


Dim FillRToZ As Variant
FillRToZ = "=If($A8<>$A9,"",If(OR(O8=$U$6,P8=$U$6,Q8=$U$6,R8=$U$6,S8=$U$6,T8=$U$6),$U$5)"
Dim rng As Range
Set rng = Range("U8:U" & erow)

Dim FillBnR As Variant
FillBnR = "=If($A8<>$A9,"",If($C8=$C$6,V7,If(D8=D9,$V$4,$Y$5)))"
Dim rng1 As Range
Set rng1 = Range("V8:V" & erow)

Dim FillOrg As Variant
FillOrg = "=If($A8<>$A9,"",If($C8=$C$6,V7,If(E8=E9,$V$4,$Y$5)))"
Dim rng2 As Range
Set rng2 = Range("W8:W" & erow)

Dim FillPjCode As Variant
FillPjCode = "=If($A8<>$A9,"",If($C8=$C$6,V7,If(F8=F9,$V$4,$Y$5)))"
Dim rng3 As Range
Set rng3 = Range("X8:X" & erow)

Dim FillTask As Variant
FillTask = "=If($A8<>$A9,"",If($C8=$C$6,V7,If(G8=G9,$V$4,$Y$5)))"
Dim rng4 As Range
Set rng4 = Range("Y8:Y" & erow)

Dim FillCRCC As Variant
FillCRCC = "=If($A8<>$A9,"",If($N8=$N$6,"",If($V8=$Y$5,$V$5,If(And($U8=$U$5,$V8=$V$4,$Y8=$X$4),$V$5,$V$6))))"
Dim rng5 As Range
Set rng5 = Range("Z8:Z" & erow)

With ws
.Range("U8").Formula = FillRToZ
FillRToZ.AutoFill , Type:=xlFillDefault
End With

With ws.Range("V8").Formula = FillBnR
FillBnR.AutoFill , Type:=xlFillDefault
End With

With ws.Range("W8").Formula = FillOrg
FillOrg.AutoFill , Type:=xlFillDefault
End With

With ws.Range("X8").Formula = FillPjCode
FillPjCode.AutoFill , Type:=xlFillDefault
End With

With ws.Range("Y8").Formulae = FillTask
FillTask.AutoFill , Type:=xlFillDefault
End With

With ws.Range("Z8").Formula = FillCRCC
FillCRCC.AutoFill , Type:=xlFillDefault
End With

FormRegion.HorizontalAlignment = xlCenter
''FormRegion.Copy
''FormRegion.PasteSpecial xlPasteValues
Range("V8").Select
End Sub

 

[Fluff]

Try it like

Sub FillFormulas2s()

Dim ws As Worksheet
Set ws = Sheets("OCT ADJ")

Dim FormRegion As Range
Set FormRegion = ws.Range("V8:Y8", Range("V8:Y8").End(xlDown))
Dim erow As Long
erow = ws.Cells(Rows.Count, "A").End(xlUp).Row


ws.Range("U8:U" & erow).Formula = "=If($A8<>$A9,"""",If(OR(O8=$U$6,P8=$U$6,Q8=$U$6,R8=$U$6,S8=$U$6,T8=$U$6),$U$5))"

ws.Range("V8:V" & erow).Formula = "=If($A8<>$A9,"""",If($C8=$C$6,V7,If(D8=D9,$V$4,$Y$5)))"

End Sub

Note that you need to double-up the quotes inside the formula

 

[CoraG]

Try it like

Sub FillFormulas2s()

Dim ws As Worksheet
Set ws = Sheets("OCT ADJ")

Dim FormRegion As Range
Set FormRegion = ws.Range("V8:Y8", Range("V8:Y8").End(xlDown))
Dim erow As Long
erow = ws.Cells(Rows.Count, "A").End(xlUp).Row


ws.Range("U8:U" & erow).Formula = "=If($A8<>$A9,"""",If(OR(O8=$U$6,P8=$U$6,Q8=$U$6,R8=$U$6,S8=$U$6,T8=$U$6),$U$5))"

ws.Range("V8:V" & erow).Formula = "=If($A8<>$A9,"""",If($C8=$C$6,V7,If(D8=D9,$V$4,$Y$5)))"

End Sub

Note that you need to double-up the quotes inside the formula

Hi there - Thank you. It is still giving me the same error with the your first line of code. Am I missing an application of some sort?

 

[CoraG]

The second one works like a charm! Thank you... Okay can you help me understand the difference? I see the difference appears to be the variable vs instead of the ws.

 

[Fluff]

Did you use the code I supplied, or modify your code?
You would get the error if the formula wasn't valid & I added the extra quotes along with an extra ) at the end

 

[CoraG]

It worked absolutely excellent!!! Thank you so very much for your help.

 

[Fluff]

You're welcome & thanks for the feedback