Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from programmers.stackexchange.com Nov 22 '11 at 17:53

This question came from our site for professional programmers interested in conceptual questions about software development.

    
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
add comment

1 Answer

up vote 2 down vote accepted

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
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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