Help using autofill in VBA to copy a formula to only cells that have data within its same row (in an adjacent column)

[Yashkar]

Hi Everyone,

When recording the macro, the recorder set the code to autofill from (b2:B39). This was fine for the sheet i was working on, but this macro is intended to be used on sheets with various lengths of data. so i need the macro to be able to identify the "end" of the data and only copy the data till there.

I got as far as this

Sub Test_2()
'
' Test_2 Macro
'

'
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(RC[-1]=R[-1]C[-1],""ommitted multiple daily submission"",""""))"
Range("B3").Select

Selection.AutoFill Destination:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
End Sub

The line in colour is causing an error "run time error 1004. Autofill method of range class failed"

How do I fix this?

Help will be greatly appreciated

 

[Yashkar]

Okay i solved it by adjusting the formula to use actual cell references and it seems to be working