I am building a macro which finds a header and then offsetting it by 1 row below i want to fill a value to the entire column. But when i Run the macro is changing the value of the offset to the Required but once it got to Selection.filldown its copying the header in the place of offset value. And also i am unable to figure out how to skip if the selection is not found. Can anyone help out with this?
Sub Macro7()
Rows("3:3").Select
Selection.Find(What:="item_width").Select
Selection.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "1"
Selection.FillDown
End Sub
As Christmas007 said, remove the .select
and .activate
:
Sub Macro7()
Dim rng As Range
Dim rws As Long
rws = Range("A" & Rows.Count).End(xlUp).Row - 2
Set rng = Rows("3:3").Find(What:="item_width")
If Not rng Is Nothing Then
rng.Offset(1, 0).FormulaR1C1 = "1"
rng.Offset(1, 0).Resize(rws).FillDown
End If
End Sub
Also the way you had the fill since it is only one cell it fills it with the cells directly above. To fill a range you need to dictated the range extents. The above is one way.