PDA

View Full Version : Solved: Error Setting Selection.Formula in a ListColumn



yourang
07-29-2010, 04:11 PM
If a ListColumn is empty and I set the formula for a selected cell in that column Excel fills the formula for the whole column, rather than just the Selection. I have duplicated this issue in a separate workbook using the following steps:
Create a Table
Insert a five or so rows
Click on (select) one of the cells in the column
Execute the setCellFormula Sub:

Sub setCellFormula()

Selection.Formula = "=myFormula()"

End Sub


Function myFormula() As Integer

myFormula = 1

End Function

Note that the whole column is filled with the numeral one
Delete the data in the cells
Enter any value in any of the cells
Select a cell other than the cell with a value in it
Re-execute the setCellFormula Sub
Note that only the selected cell is filled with the numeral oneOddly... This doesn't seem to be an issue on all PCs. My laptop executes without any errors, but I've tried it on a separate laptop and a PC and they both express the anomaly.

Any thoughts?

p45cal
08-01-2010, 04:32 AM
This is by design, certainly in excel 2007. If you do Ctrl+z immediately after you've entered the formula it will leave just the formula you typed in that single cell.
There are option you can set: either you can get to them by clicking one of the little boxes next to the cell if they're showing, otherwise go to Office Button, Excel options…, Proofing, Autocorrect options…, Autoformat as you type tab, and look at the last checkbox:'Fill Formulas in tables to create calculated columns'.

yourang
08-02-2010, 07:39 AM
Thanks! That was the root of the issue. Here's a link to an article with more details for anyone who might need it: worldstart.com/tips/tips.php/5260