Autofill Variable Range

caddcode · Jan 31st 2023

Hello,

I've got the below set of code which I'll be calling into a main portion of code and will be the same for each month of the year. The worksheets for each month are separate and the data placement within each sheet can vary - in order to look for the correct data I've come up with the below code which looks at specific headers ('First Name' and 'Range 1 (Name-System)') and from there it will use the cell references within the concatenate formula that would ultimately be pasted below the 'Range 1..." header.

Where I'm a bit stuck is with the autofill function. My macro steps through everything just fine and is placing the initial formula in the correct cell but in order to autofill it down to the end of the data it requires a range - since each sheet has a different number of columns I can't just indicate U2:U or T2:2 etc. and need this to be variable based on where my current active cell is (where that concatenate formula is currently being inserted correctly).

I'm self taught for VBA coding so I'm sure there are areas my code can be cleaned/simplified as well so any help is appreciated!

Thanks!

Code

Sub JanFormat()
    Dim WB2 As Workbook, WS2 As Worksheet, WS2_1 As Worksheet
    
    Dim i As Integer, lastRow As Long, MyColl As Collection, MyColl2 As Collection, myIterator As Variant, myIterator2 As Variant, FN As Integer, LN As Integer, FileN As Integer

    Set WB2 = Workbooks("Working Updates_WORKING COPY.xlsx")
    Set WS2_1 = WB2.Sheets("January Worked")
    Set MyColl = New Collection
    Set MyColl2 = New Collection
    
    With WB2.Sheets("January Worked")
        WS2_1.Activate
        
        Range("A2").Activate
            If ActiveCell = "" Then
                Exit Sub
            Else
            End If
        Range("A1").End(xlToRight).Offset(, 1).Select
            ActiveCell.FormulaR1C1 = "Range 1 (Name-System)"
        
        MyColl.Add "First Name"
        MyColl2.Add "Range 1 (Name-System)"
        
        lastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

        For i = 1 To 200
            For Each myIterator In MyColl
                If Cells(1, i) = myIterator Then
                    For j = 1 To 200
                        For Each myIterator2 In MyColl2
                         If Cells(1, j) = myIterator2 Then
                            Range("B2").End(xlToRight).Offset(, 1).Select
                            FN = i - j
                            LN = (i + 1) - j
                            FileN = (i + 2) - j
                            ActiveCell.Formula = "=CONCATENATE(RC[" & FN & "], "" "", RC[" & LN & "], ""-"", RC[" & FileN & "])"
                            Selection.Interior.Color = xlNone
                            Selection.AutoFill Destination:=Range(Variable & Range("E" & Rows.Count).End(xlUp).Row)
                            Range(Selection, Selection.End(xlDown)).Select
                         End If
                        Next
                    Next
                End If
            Next
        Next
    End With
End Sub

Display More

**Carim** · Jan 31st 2023

Hi and Welcome to the Forum

Why not using Copy to your destination ...

caddcode · Jan 31st 2023

Hey Carim

Thanks for the welcome and suggestion. I hadn't even thought of leveraging the copy function but wouldn't I still need to provide a range for the destination? I'm not sure if the 'Variable' area in my current code would be replaced with anything that would look at current active cell versus having to specifically call out a range such as U2:U that could then be T2:T in my next tab.

Using Copy...would I be able to have logic to have it paste down to lastRow in whatever the current column is?

Thanks!

**Carim** · Jan 31st 2023

Below is an example which might help

Code

Sub TestCopy()
Dim colNb As Long
Dim lastrow As Long
colNb = 21              '  i.e Column U
lastrow = 15            '  as an example
' Copy Formula from U2 to range U3:U15   '
Cells(2, colNb).Copy Destination:=Range(Cells(3, colNb), Cells(lastrow, colNb))
End Sub

caddcode · Jan 31st 2023

Thanks Carim!

Looks like it's working! I'll have to trim the cells due to the way the data is formatted and put in logic to close the loop but that is easy enough - truly appreciate the help!

Code

Sub JanFormat()
    Dim WB2 As Workbook, WS2 As Worksheet, WS2_1 As Worksheet
    
    Dim i As Integer, lastRow As Long, colNb As Long, MyColl As Collection, MyColl2 As Collection, myIterator As Variant, myIterator2 As Variant, FN As Integer, LN As Integer, FileN As Integer
    Set WB2 = Workbooks("Working Updates_WORKING COPY.xlsx")
    Set WS2_1 = WB2.Sheets("January Worked")
    Set MyColl = New Collection
    Set MyColl2 = New Collection
    
    With WB2.Sheets("January Worked")
        WS2_1.Activate
        
        Range("A2").Activate
            If ActiveCell = "" Then
                Exit Sub
            Else
            End If
        Range("A1").End(xlToRight).Offset(, 1).Select
            ActiveCell.FormulaR1C1 = "Range 1 (Name-System)"
        
        MyColl.Add "First Name"
        MyColl2.Add "Range 1 (Name-System)"
        
        lastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

       For i = 1 To 200
            For Each myIterator In MyColl
                If Cells(1, i) = myIterator Then
                    For j = 1 To 200
                        For Each myIterator2 In MyColl2
                         If Cells(1, j) = myIterator2 Then
                            Range("B2").End(xlToRight).Offset(, 1).Select
                            FN = i - j
                            LN = (i + 1) - j
                            FileN = (i + 2) - j
                            colNb = j
                            ActiveCell.Formula = "=CONCATENATE(RC[" & FN & "], "" "", RC[" & LN & "], ""-"", RC[" & FileN & "])"
                            Selection.Interior.Color = xlNone
                            Cells(2, colNb).Copy Destination:=Range(Cells(3, colNb), Cells(lastRow, colNb))
                            Range(Selection, Selection.End(xlDown)).Select
                         End If
                        Next
                    Next
                End If
            Next
        Next
    End With
End Sub

Display More

**Carim** · Jan 31st 2023

Thanks for your Thanks AND for the Like

Autofill Variable Range

Participate now!

Share

Tags