Autofill after finding a text. Dynamic approach

[Alroj]

Hi Team,
I have a question about Autofill to the last row after identifying a text. This text is unique and shows in a different cell each time

 'Search for the specific TEXT (= "ABCD") first

 Range("A6:AZ1000").Select
     Selection.Find(What:="ABCD", After:=ActiveCell, LookIn:=xlFormulas2 _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
     MatchCase:=False, SearchFormat:=False).Select
 
 'Then select a range from that point and the next 6 rows to the right

    Range(Selection, Selection.Offset(0, 5)).Select

' Then AUTOFILL down for several rows till find the last row

    Selection.AutoFill Destination:=Range("O10:T" & lastrow)

The situation from the code above is that the range starts at "O10" and this is not the case every month. Ideally, I would like this range to start from Range(Selection, Selection.Offset(0, 5)) all the way to the LastRow.
Something like: Selection.AutoFill Destination:=Range(Range(Selection, Selection.Offset(0, 5)) & lastrow)

Would anyone share their wisdom and give me some guidance on how to implement this please

Much appreciated

 

[Joe4]

Assuming that you have actually calculated the value of lastrow somewhere in your code already, try this:

Range("A6:AZ1000").Find(What:="ABCD", After:=ActiveCell, LookIn:=xlFormulas2 _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
     MatchCase:=False, SearchFormat:=False).Select
 
Dim rng As Range
Set rng = Range(ActiveCell, Cells(lastrow, ActiveCell.Column + 5))

Selection.AutoFill Destination:=rng

 

[Alroj]

Thank you for replying Joe4,

It worked till certain extend. The filldown worked but the first column that was initially selected in the range remained empty as in the screenshot below

Any ideas how to medify the script to include the column highlighted in yellow please?

Cheers

 

[Joe4]

I would need the following information in order to help you debug this:

1. Your complete VBA code for this procedure, so I can see the other calculations, like "lastrow" and what other things may be going on.
2. Sample data before the code is run
3. Expected output of your data after the code is run.

For item 2, I need the data in a format which I can copy/paste to my computer to test (I cannot copy data out of an image).
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.