Search code examples
vbaexceloffset

Vba is taking the original Selection even after offset with "itemwidth"


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

Solution

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