Hi,
I am copying formulas in a table in the same column via VBA.
How do I prevent the formulas automatically being copied in ALL of the cells of the column?
Example:
After running this in a sub all cells in column B of the table have the same formula "=4+4"
Sub TestFormulas() Range("B2").formula = "=1+1" Range("B3").formula = "=2+2" Range("B4").formula = "=3+3" Range("B5").formula = "=4+4" End Sub
How do I prevent that ?
There are a few methods on this website you might want to employ to prevent table formulas from auto-populating:
http://office.microsoft.com/en-us/ex...#_Toc295810735
Is that something you can work with?
Are you sure? I dont get that???? Ignore me, i wasnt trying it on a table.
Hope this helps
Sometimes its best to start at the beginning and learn VBA & Excel.
Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
Available for remote consultancy work PM me
Thanks Ron,
That setting works okay, but it seems to be an Excel setting rather than a workbook setting.
That means that when I distribute my workbook, I have to make sure users change that setting too.
Would there possibly be a way to have the same result on a workbook level, of (even better) on a table level ?
Well...maybe a "stupid Excel trick"?
Try this:
That code prevents Excel from detecting a formula pattern.Sub TestFormulas() Range("B2").Formula = "A" Range("B3").Formula = "B" Range("B2").Formula = "=1+1" Range("B3").Formula = "=2+2" Range("B4").Formula = "=3+3" Range("B5").Formula = "=4+4" End Sub
Is that something you can work with?
That seems to be the trick!
First take the values out of an order and then fill the cell with formulas.
Thanks !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks