Need help on set range and autofill

123.xlsxi am try create a macro to search a keyword and use number of cell data below the keyword as a range for autofill. Then search another keyword and autofill the data the keyword but autofill till the range u set it. i attach a example of it

Cells.Find(What:="NEIGHBORHOOD", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select


Dim Myrange As Range
Set Myrange = Range(ActiveCell, ActiveCell.End(xlDown))


Cells.Find(What:="Miles", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(1, 0).Select
ActiveCell.AutoFill Destination:=Range(ActiveCell & Myrange)

Hi, chriskingkiller,

IŽd narrow the area to search down form all cells (which includes all those which are blanks) to ActiveSheet.UsedRange or any Column:

Sub EF973716()
'http://www.excelforum.com/excel-programming-vba-macros/973716-need-help-on-set-range-and-autofill.html
Dim rngStart As Range
Dim rngMiles As Range
Dim lngCount As Long

Set rngStart = Cells.Find(What:="NEIGHBORHOOD", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

If Not rngStart Is Nothing Then
  lngCount = rngStart.End(xlDown).Row - rngStart.Row
  Set rngMiles = Cells.Find(What:="Miles", After:=rngStart, LookIn:=xlFormulas, _
      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)
  If Not rngMiles Is Nothing Then
    Set rngMiles = rngMiles.Offset(1, 0)
    rngMiles.AutoFill Destination:=rngMiles.Resize(lngCount, 1)
  End If
End If

Set rngMiles = Nothing
Set rngStart = Nothing

End Sub

Ciao,
Holger