Fill down formula

[Aviles]

I have some simple code that copies data from sheet 'Auto', pastes in cell C2 and then auto-fills to C20000:

Range("C2").Select
ActiveCell.FormulaR1C1 = "=Auto!R[1]C[32]"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C20000")

The issue with this is that the columns with data in sheet 'Auto' are not always in the same place. Am I able to use your code to first find a specific column heading in sheet 'Auto' (headings are always in row 2), which in this case is "Amount" and then use my code below to copy, paste and auto-fill the data below that specified heading?

There are only around 100 headings in sheet 'Auto' and I will need to do this process for around 15 different columns.

Thank you.

 

[Caleeco]

Hi Aviles,

Would something like this work for you?

Sub FindHeader_and_CopyFormula()
    Dim wsAuto As Worksheet
    Dim r As Range, RNG As Range
    Dim lc As Long
    
    Set wsAuto = Sheets("Auto")
    With wsAuto
        lc = .Cells(2, .Columns.Count).End(xlToLeft).Column
        Set RNG = .Range(Cells(2, 1), Cells(2, lc))
        
        For Each r In RNG
            Select Case r.Value
                Case "Amount"
                    .Range("C2").Formula = r.Offset(1).Formula
                    .Range("C2").AutoFill Destination:=.Range("C2:C20000")
                    
                Case "Other Header"
                
                Case "Another Header"
                
                'add as many as you want
            End Select
        Next r
    End With
End Sub

Let me know
Thanks
Caleeco

 

[Aviles]

Hi Aviles,

Would something like this work for you?

Let me know
Thanks
Caleeco

Hi @Caleeco, thanks for your help on this. Unfortunately I'm getting a Run-time error: '1004': Method 'Range' of object'_Worksheet' failed

When I hit Debug, it takes me to this part of the code:

Set RNG = .Range(Cells(2, 1), Cells(2, lc))

Here is the code I'm using (I ended up changing the sheet name to 'AutoFX' instead):

    Dim wsAutoFX As Worksheet
    Dim r As Range, RNG As Range
    Dim lc As Long
    
    Set wsAutoFX = Sheets("AutoFX")
    With wsAutoFX
        lc = .Cells(2, .Columns.Count).End(xlToLeft).Column
        Set RNG = .Range(Cells(2, 1), Cells(2, lc))
        
        For Each r In RNG
            Select Case r.Value
                Case "CCY Pair"
                    .Range("A2").Formula = r.Offset(1).Formula
                    .Range("A2").AutoFill Destination:=.Range("A2:A20000")
                    
                Case "Dealt CCY"
                    .Range("B2").Formula = r.Offset(1).Formula
                    .Range("B2").AutoFill Destination:=.Range("B2:B20000")
                
                Case "Dealt AMT"
                    .Range("C2").Formula = r.Offset(1).Formula
                    .Range("C2").AutoFill Destination:=.Range("C2:C20000")
                    
                Case "Direction"
                    .Range("D2").Formula = r.Offset(1).Formula
                    .Range("D2").AutoFill Destination:=.Range("D2:D20000")
                
                Case "Client Spot Rate"
                    .Range("E2").Formula = r.Offset(1).Formula
                    .Range("E2").AutoFill Destination:=.Range("E2:E20000")
                    
                Case "Order Execution Time"
                    .Range("G2").Formula = r.Offset(1).Formula
                    .Range("G2").AutoFill Destination:=.Range("G2:G20000")
                    
                Case "SEC Account ID"
                    .Range("K2").Formula = r.Offset(1).Formula
                    .Range("K2").AutoFill Destination:=.Range("K2:K20000")
                    
                Case "Order Type"
                    .Range("L2").Formula = r.Offset(1).Formula
                    .Range("L2").AutoFill Destination:=.Range("L2:L20000")
                    
                Case "Value Date"
                    .Range("V2").Formula = r.Offset(1).Formula
                    .Range("V2").AutoFill Destination:=.Range("V2:V20000")
                    
                Case "AutoFx Order ID"
                    .Range("Y2").Formula = r.Offset(1).Formula
                    .Range("Y2").AutoFill Destination:=.Range("Y2:Y20000")
                    
                'add as many as you want
            End Select
        Next r
    End With

I'm thinking maybe I wasn't that clear in my original request, apologies. The headings that I'm looking for are located somewhere in row 2 of sheet 'AutoFX', and when the heading is found I want the formation below the heading to be pasted in sheet 'Best'.

So in the first example, I'm looking for the heading "CCY Pair" in sheet 'AutoFX' which is somewhere in row 2, and I would like the information in the cell just below this heading to be pasted in cell A2 of sheet 'Best', then auto-filled to cell A20000.

Also, I would like to enter the following formula in cell G2 of sheet 'Best', where the heading I'm looking for in sheet 'AutoFX' is 'Order Execution Time': =IF(AutoFX!W3="","",ToDate(AutoFX!W3))
How would this formula be represented in code when I don't know the location of the heading? I used W3 just as an example, however this location can be different.

Thanks.

 

[Aviles]

Is anyone able to help me with this please? Thank you.

 

[Caleeco]

Hello Aviles,

Try this instead:

Sub Test()
    Dim wsAutoFX As Worksheet, wsBest As Worksheet
    Dim r As Range, RNG As Range
    Dim lc As Long
    
    Set wsAutoFX = Sheets("AutoFX")
    Set wsBest = Sheets("Best")
    
    wsAutoFX.Activate
    With wsAutoFX
        lc = .Cells(2, .Columns.Count).End(xlToLeft).Column
        Set RNG = .Range(Cells(2, 1), Cells(2, lc))
        
        For Each r In RNG
            Select Case r.Value
                Case "CCY Pair"
                    wsBest.Range("A2").Formula = r.Offset(1).Formula
                    wsBest.Range("A2").AutoFill Destination:=wsBest.Range("A2:A20000")
                    
                Case "Dealt CCY"
                    wsBest.Range("B2").Formula = r.Offset(1).Formula
                    wsBest.Range("B2").AutoFill Destination:=wsBest.Range("B2:B20000")
                
                Case "Dealt AMT"
                    wsBest.Range("C2").Formula = r.Offset(1).Formula
                    wsBest.Range("C2").AutoFill Destination:=wsBest.Range("C2:C20000")
                    
                Case "Direction"
                    wsBest.Range("D2").Formula = r.Offset(1).Formula
                    wsBest.Range("D2").AutoFill Destination:=wsBest.Range("D2:D20000")
                
                Case "Client Spot Rate"
                    wsBest.Range("E2").Formula = r.Offset(1).Formula
                    wsBest.Range("E2").AutoFill Destination:=wsBest.Range("E2:E20000")
                    
                Case "Order Execution Time"
                    wsBest.Range("G2").Formula = r.Offset(1).Formula
                    wsBest.Range("G2").AutoFill Destination:=wsBest.Range("G2:G20000")
                    
                Case "SEC Account ID"
                   wsBest.Range("K2").Formula = r.Offset(1).Formula
                    wsBest.Range("K2").AutoFill Destination:=wsBest.Range("K2:K20000")
                    
                Case "Order Type"
                    wsBest.Range("L2").Formula = r.Offset(1).Formula
                    wsBest.Range("L2").AutoFill Destination:=wsBest.Range("L2:L20000")
                    
                Case "Value Date"
                    wsBest.Range("V2").Formula = r.Offset(1).Formula
                    wsBest.Range("V2").AutoFill Destination:=wsBest.Range("V2:V20000")
                    
                Case "AutoFx Order ID"
                    wsBest.Range("Y2").Formula = r.Offset(1).Formula
                    wsBest.Range("Y2").AutoFill Destination:=wsBest.Range("Y2:Y20000")
                    
                'add as many as you want
            End Select
        Next r
    End With
End Sub

Caleeco