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.

  • 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 Nov 22 '11 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 Nov 23 '11 at 0:20

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
  • This works perfectly! Thanks so much. I was searching for hours for a command like that. – user41293 Nov 23 '11 at 17:31
  • You are welcome! Glad to help. – CharlieRB Nov 23 '11 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.