My VBA code drags a formula down even if not asked to and I don't understand why

Question

I really do believe that my VBA code has turned into a sentient entity and is doing stuff on its own even when not asked to!

Joke aside, I've been working on this code for the past week and there is something I do not understand. I first wrote this code for a previous Excel file. And it works. it does what I want it to do.

Then, I decided to use that same code for another Excel doc. The only thing that I have changed is references. I just copy/pasted the code in the new Excel file. To make it work properly, I just changed the references used by VBA to get data from the different tabs of my file.

Here's the part of the code that I have trouble with:

Dim LastRow As Long
    
    Sheets("All").Select
    
    Range("A3:DB50000").ClearContents
    
    Application.Goto Reference:="Ref_EPG" 'Goes to the Ref_EPG table and selects it
    
If Not IsEmpty(ActiveCell.Value) Then

    Selection.Copy 'Copies the Ref_EPG table

    Sheets("All").Select 'Goes to the "All" tab

    Range("A3").Select 'Selects cell A3
    
    ActiveSheet.Paste 'Paste the Reference Numbs in the rows

    Range("B3").Select 'Selects cell B3
    
    Application.CutCopyMode = False 'Clears the clipboard
    
    ActiveCell.FormulaR1C1 = _
        "=IF(LEN(VLOOKUP(RC1,Table_Budget_EPG,EPG!R1C,FALSE))=0,"""",VLOOKUP(RC1,Table_Budget_EPG,EPG!R1C,FALSE))" 'Sets up the VLOOKUP function and if there is nothing in the source cell, returns blank instead of 0.
    
    LastRow = Sheets("All").Range("A" & Rows.Count).End(xlUp).Row 'Determines the last row used in column A
    
    If LastRow > 1 Then
    
        Sheets("All").Range("B3").AutoFill Destination:=Sheets("All").Range("B3:B" & LastRow) 'Autofills the VLOOKUP function from B3 to the last used row of Ref_Numbers
    
    End If

End If

The line of this code that is driving me crazy is this one:

ActiveCell.FormulaR1C1 = _
"=IF(LEN(VLOOKUP(RC1,Table_Budget_EPG,EPG!R1C,FALSE))=0,"""",VLOOKUP(RC1,Table_Budget_EPG,EPG!R1C,FALSE))" 'Sets up the VLOOKUP function and if there is nothing in the source cell, returns blank instead of 0.

This line is just supposed to insert a formula in a cell. That's all. But I don't know why, when I run the code step by step using F8, and I reach this line, it doesn't just insert the formula, it also drags it down. Which is not supposed to happen at that moment of the code.

It works perfectly in my other Excel file, why not here?

Thank you very much for your help.

Love you guys

This happens when your data is in a table and you add the formula into a column that has yet no data. It's not a VBA issue, same happens when you enter the formula manually in Excel — FunThomas, Sep 26 '22 at 13:39
Side note: try to [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Sep 26 '22 at 14:46
Related: https://www.mrexcel.com/board/threads/prevent-autofill-of-formulas-in-excel-tables-w-vba.580576/ You can turn off that setting before populating the formula. — Tim Williams, Sep 26 '22 at 15:34

score 1 · Accepted Answer · answered Sep 26 '22 at 15:38

See https://learn.microsoft.com/en-us/office/vba/api/excel.autocorrect.autofillformulasinlists

Dim autoFillTables as Boolean

'store the setting
autoFillTables = Application.AutoCorrect.AutoFillFormulasInLists
'make sure it's Off
Application.AutoCorrect.AutoFillFormulasInLists = False

'Sets up the VLOOKUP function and if there is nothing in the source cell, 
'   returns blank instead of 0.
ActiveCell.FormulaR1C1 = _
        "=IF(LEN(VLOOKUP(RC1,Table_Budget_EPG,EPG!R1C,FALSE))=0,""""," & _
        "VLOOKUP(RC1,Table_Budget_EPG,EPG!R1C,FALSE))" 

'Restore the setting
Application.AutoCorrect.AutoFillFormulasInLists = autoFillTables

My VBA code drags a formula down even if not asked to and I don't understand why

1 Answers1