VBA finding column header and autofill contents by creating dynamic range

afkzaman

New Member
Joined
Aug 22, 2017
Messages
18
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.
Code:
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
 
It's because your data starts on row 4. If lrow was 100 & lcol was 10 this
Code:
ws.Cells(4, lcol).Resize(lrow)
would effectively be
Code:
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
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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:
Code:
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.
 
Last edited:
Upvote 0
2 questions
1) What is the value in B1?
2) what line of code gives the error?
 
Upvote 0
1. B1 = Column (AA8), this is the column where I find "y"

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

Thank you.
 
Upvote 0
Try
Code:
        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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top