2

I am working on a macro that inserts formulas into a cell in an Excel table.

The Excel table does the automatic filling of columns and fills all the cells in that column with the formula, but all I want is one cell to have the formula. I cannot just turn off automatic formula for tables as I need to have other people use this worksheet on their systems.

Is there a way to turn off the automatic filling of formulas in a table using VBA in a macro? It just needs to be temporary: I just want to turn it off, put in my formulas, then turn it back on.

Improve this question
2
  • You have said "automatic filling" and "automatic formula" in your questions. Is that on purpose? Did you mean "automatic calculation"? Just trying to understand better. Can you post the macro code so we can see what you are doing?
    – CharlieRB
    Commented Nov 22, 2011 at 21:55
  • It isn't "automatic calculation" that is different. "Automatic filling of formula" is a feature of tables that automatically fills the whole column with a formula when you enter a formula in a column. The code below is what I use to enter the formula in a cell, then it automatically fills the whole column, which is the behavior I am trying to disable: formulaStr = "=IF([@[Evaluation Score]]=""N/A"",""" & NAString & ""","""")" Sheets("Scorecard").Cells(foundCell.Row, 9).Formula = formulaStr
    – user41293
    Commented Nov 23, 2011 at 0:20

1 Answer 1

Reset to default
3

Thanks for the additional info. I am pretty sure this is what you are looking for; to turn off the AutoFill add the following before your code. To turn it back on, use the same line after your code with "True". Let me know if that did it. 

Application.AutoCorrect.AutoFillFormulasInLists = False
Improve this answer
2
  • This works perfectly! Thanks so much. I was searching for hours for a command like that.
    – user41293
    Commented Nov 23, 2011 at 17:31
  • You are welcome! Glad to help.
    – CharlieRB
    Commented Nov 23, 2011 at 19:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .