Flashfill formula from variable column

[Guest1337]

Hi all!

For a dynamic analysis, I need to add a new column to my data set every month. This new column should continue the formula from the column to the left of it, e.g. one column contains the formula: "=EOMONTH(P2;1)", which should then become "EOMONTH(Q2;1)" in the new column to the right of it.

Since there are several analyses on the sheet, I cannot search for the last column, but I search for a term "[1]", next to which a new column is to be inserted and the formula from the column to the left is to be dragged into this new column. This way, I can always search for the same term, as it always moves along.

The data record looks something like this:

			[1]
01/01/2024	01/02/2024	01/03/2024
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5

I have the code so far that it finds the term "[1]" and inserts a column to the left of it, but now I can't get the formula there.

Hier der Code:
Dim ow As Worksheet
Dim rng1 As Range
Dim TargetCol As Range
Dim SourceCol As Range

Set ow = ThisWorkbook.Sheets("Overview")
Set rng1 = ow.Cells.Find("[1]", LookIn:=xlValues)

ow.Range(rng1, ow.Cells(ow.Rows.Count, rng1.Column)).EntireColumn.Insert

Set TargetCol = rng1.Offset(0, -1)
Set SourceCol = rng1.Offset(0, -2)

SourceCol.AutoFill Destination:=TargetCol, Type:=xlFillDefault
TargetCol.Formula = "=" & SourceCol.Address

Thanks a lot in advance!

 

[Alex Blakenburg]

I am not clear on what you are trying to achieve. The autofill copies the formula across but then the last line overwrites that.
In terms of just fixing the syntax try this:

Sub FillFormulaRight()

    Dim ow As Worksheet
    Dim rng1 As Range
    Dim TargetCol As Range
    Dim SourceCol As Range
    
    Set ow = ThisWorkbook.Sheets("Overview")
    Set rng1 = ow.Cells.Find("[1]", LookIn:=xlValues)
    
    rng1.EntireColumn.Insert
    
    Set TargetCol = rng1.Offset(0, -1)
    Set SourceCol = rng1.Offset(0, -2)
    
    SourceCol.AutoFill Destination:=SourceCol.Resize(1, 2), Type:=xlFillDefault
    'TargetCol.Formula = "=" & SourceCol.Address       ' Commented out will overwrite the previous output

End Sub

 

[Guest1337]

Sorry for the confusion!

This is what the macro is supposed to do:

			[1]
01/01/2024	=EOMONTH(O2;1)	=EOMONTH(P2;1)
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5

1. Look for the string [1]
2. Insert a new column to the left of [1], like this:

				[1]
01/01/2024	=EOMONTH(O2;1)	=EOMONTH(P2;1)
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5

3. Flashfill the formula from the column to the left of the newly inserted column into this newly inserted column, like this:
The numbers 1-5 are placeholders others formulas (VLOOKUPs and SUMIFS, etc.). They are supposed to be flashfilled into this new column.

				[1]
01/01/2024	=EOMONTH(O2;1)	=EOMONTH(P2;1)	=EOMONTH(Q2;1)
1	1	1	1
2	2	2	2
3	3	3	3
4	4	4	4
5	5	5	5

 

[Alex Blakenburg]

Then you also want to move (offset) down 1 row from your find position:

Sub FillFormulaRight()

    Dim ow As Worksheet
    Dim rng1 As Range
    Dim TargetCol As Range
    Dim SourceCol As Range
    
    Set ow = ThisWorkbook.Sheets("Overview")
    Set rng1 = ow.Cells.Find("[1]", LookIn:=xlValues)
    'Set rng1 = ow.Cells.Find("a", LookIn:=xlValues)
    
    rng1.EntireColumn.Insert
    
    Set TargetCol = rng1.Offset(1, -1)
    Set SourceCol = rng1.Offset(1, -2)
    
    SourceCol.AutoFill Destination:=SourceCol.Resize(1, 2), Type:=xlFillDefault

End Sub

 

[Alex Blakenburg]

See if this does what you are after. The previous post only brings across the heading row.

Sub FillFormulaRight()

    Dim ow As Worksheet
    Dim rng1 As Range
    Dim TargetCol As Range
    Dim SourceCol As Range
    Dim SourceLastRow As Long
    
    Set ow = ThisWorkbook.Sheets("Overview")
    Set rng1 = ow.Cells.Find("[1]", LookIn:=xlValues)
    
    rng1.EntireColumn.Insert
    
    Set TargetCol = rng1.Offset(1, -1)
    Set SourceCol = rng1.Offset(1, -2)
    SourceLastRow = ow.Cells(Rows.Count, SourceCol.Column).End(xlUp).Row
    Set SourceCol = SourceCol.Resize(SourceLastRow - SourceCol.Row + 1, 1)
    
    SourceCol.AutoFill Destination:=SourceCol.Resize(, 2), Type:=xlFillDefault

End Sub

 

[Guest1337]

That's a progress, thank you! It took the formula for the date from the left to the new column but not the other formulas, see below:
How do I get all of them to the right?

				[1]
01/01/2024	=EOMONTH(O2;1)	=EOMONTH(P2;1)	=EOMONTH(Q2;1)
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5

 

[Alex Blakenburg]

You might need to refresh your screen, there is a post after that one.

 

[Guest1337]

That's it! Thanks a lot, Alex!

 

[Alex Blakenburg]

You're welcome. Glad I could help.