PDA

View Full Version : Autofill formula help



caoliz
04-24-2017, 07:28 AM
Hello,

I am trying to write a code that will autofill formulas in columns H, I, and J to the end of the data set (there is data in columns A-G). I have the below so far:

Sub Autofill()
'
' Autofill Macro
'


Sheets("Claims Filed Data").Select
Range("H4:H" & Lastrow).Formula = "=(NETWORKDAYS(F4,E4))-1"
Range("I4:I" & Lastrow).Formula = "=IF(H4<=2,"Yes", "No")"
Range("K4:K" & Lastrow).Formula = "=E4+(6-WEEKDAY(E4))"


End Sub


I get the run-time error '1004' - Method 'Range' of object'_Global' failed with the above code. Will someone please help?

Thank you!

mana
04-24-2017, 07:37 AM
Lastrow ?

mana
04-24-2017, 07:39 AM
> "=IF(H4<=2,"Yes", "No")"

"=IF(H4<=2,""Yes"", ""No"")"

jolivanes
04-24-2017, 07:01 PM
Would this work?

Dim lr As Long
lr = Cells(Rows.Count, 5).End(xlUp).Row '<------ Last used cell in Column 5. Change as required
With Range("H4:H" & lr)
.Formula = "=(NETWORKDAYS(RC[-2],RC[-1]))-1"
.Offset(, 1).Formula = "=IF(RC[-1]<=2,""Yes"", ""No"")"
.Offset(,3).Formula = "=RC[-6]+(6-WEEKDAY(RC[-6]))"
End With


Note. See the doubling up of the quotation marks as mana showed in his post.