VBA finding column header and autofill contents by creating dynamic range

[afkzaman]

End Sub

Hi all, I'm trying to write a macro where i need to find the column header 'dec-26' in row 4 and extend the date till 'dec-31', i.e. add another 60 months and autofill the newly created cells so that the contents/formulas remain intact. I wrote a small part of macro using 'record macro' which serves as the basic but far from what i want.

Sub Testmacro()
Dim ws As Worksheet
Dim lcol As Long, lrow As Long

'set the relevant worksheet
    Set ws = Workbooks("sample.xlsx").Sheets("data")

'find the date cell in row 4
    Cells.Find(What:="dec-26", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
     
'expand date till dec-31 (need to insert new columns as there's only one blank column after G4)

        Selection.AutoFill Destination:=Range("G4:BO4"), Type:=xlFillDefault

'autofill all the contents/formulas to the newly created cells
        Range("G5:G31").Select
        Selection.AutoFill Destination:=Range("G5:BO31"), Type:=xlFillDefault
   
End Sub

What I'm trying to do:

1. Auto-increment column headers till 'dec-31' after finding the header 'dec-26', but not by hard coding range "G4:BO4". From cell I4 the series 'Jan-17 to Dec-26' starts again for another 12 times under different main headings with one blank column between them. When I run my macro it does not insert any new columns.

2. Autofill the contents in the newly created cells by using dynamic range, not by hard-coded range. Row 5 to 30 need to be copied in the new cells.

I'm learning and trying to figure out the macro myself but It's bit difficult for a beginner. Any sort of help will be highly appreciated. Thanks in advance

 

[Fluff]

It's because your data starts on row 4. If lrow was 100 & lcol was 10 this

ws.Cells(4, lcol).Resize(lrow)

would effectively be

Range("J4").Resize(100)
ie
Range("J4:J103")

By using lrow-3 we take the range back to J100, which would be the last row of data.
HTH

 

[afkzaman]

@Fluff

Yes, i understand now. I assumed it but now it's more clear. Thank you once again.


Regards, Zaman

 

[Fluff]

Glad to help

 

[afkzaman]

Hi @Fluff

Your previous solution worked great and now I got stuck again with another problem. It's basically the same as above, only this time I need to insert rows and Autofill the newly created rows. I can insert the rows but cannot copy, it shows Run-time error 1004. Here are the codes:

Sub Scenario()

    Dim ws As Worksheet
    Dim lcol As Long
    Dim lrow As Long
    Dim Ans As Long
    Dim Cnt As Long
    Dim acell As Range
    
    
'set the relevant worksheet
    Set ws = Sheets("Scenario")
    lcol = ws.Cells.Find("*", after:=ws.Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

'find y in the corresponding cells (this sheet has three y and those are linked to cell B1)
    Ans = WorksheetFunction.CountIf(ws.Columns(Range("B1")), "y")

' It finds column 27 basically
    Set acell = ws.Range("AA1")

' loop over all the date cells containing y
    If Ans >= 1 Then
        For Cnt = 1 To Ans
            Set acell = ws.Columns(Range("B1")).Find(What:="y", after:=acell, LookIn:=xlValues, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            
        lrow = acell.Row
        
' insert 5 rows in between year 2025 and 2026, I did it this way as it keeps formulas intact when copy rows afterwards
        ws.Rows(lrow - 1).Resize(5, lcol).Insert
 
' Copy rows startig from row containing year 2025
        ws.Cells(lrow - 2, 1).Resize(lcol).AutoFill Destination:=ws.Cells(lrow - 2, 1).Resize(5, lcol), Type:=xlFillDefault
    
        Next Cnt
    End If

End Sub

I am wondering if you can help me with this macro. Thanks in advance.

Regards.

 

[Fluff]

2 questions
1) What is the value in B1?
2) what line of code gives the error?

 

[afkzaman]

1. B1 = Column (AA8), this is the column where I find "y"

2.

ws.Cells(lrow - 2, 1).Resize(lcol).AutoFill Destination:=ws.Cells(lrow - 2, 1).Resize(5, lcol), Type:=xlFillDefault

Thank you.

 

[Fluff]

Try

        ws.Cells(lrow - 2, 1).Resize[COLOR=#ff0000](, lcol)[/COLOR].AutoFill Destination:=ws.Cells(lrow - 2, 1).Resize([COLOR=#ff0000]6[/COLOR], lcol), Type:=xlFillDefault

 

[afkzaman]

It worked, I understood the logic now. Thank you very much for your kind help again.

 

[Fluff]

You're welcome