AutoFill If Formulas - Help

CoraG

New Member
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

Dim FormRegion As Range
Set FormRegion = Sheets("OCT ADJ").Range("V8:Y8", Range("V8:Y8").End(xlDown))
Dim erow As Long

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

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

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fluff

MrExcel MVP, Moderator
Try it like
Code:
``````Sub FillFormulas2s()

Dim ws As Worksheet

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

New Member
Try it like
Code:
``````Sub FillFormulas2s()

Dim ws As Worksheet

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

New Member
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

MrExcel MVP, Moderator
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

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

Fluff

MrExcel MVP, Moderator
You're welcome & thanks for the feedback

Replies
0
Views
180
Replies
5
Views
312
Replies
1
Views
141
Replies
1
Views
166
Replies
3
Views
230

1,196,027
Messages
6,012,953
Members
441,740
Latest member
abaz21

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