1

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
Chrismas007
  • 6,085
  • 4
  • 24
  • 47
Tarun Aryan
  • 25
  • 6
  • 2
    Using `.Select` is notoriously bad... See [This Post](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for more information. – Chrismas007 Dec 03 '15 at 22:04

1 Answers1

2

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • how to tell it to use the original data range? . I mean there is already data with some number which i am replacing with 1. So there is no fixed range it differs from file to file – Tarun Aryan Dec 03 '15 at 22:18
  • @TarunAryan glad I could help, please consider marking as correct. Just hit the large green check mark left of the answer. – Scott Craner Dec 03 '15 at 22:37
  • If the search phrase is not found its returning a Runtime error 91. Could you please help on how handle that? – Tarun Aryan Dec 03 '15 at 22:45