Making multiple cells absolute at once
I set up a very large workbook. I have linked the formulas of one sheet to
another, however, I need the columns to be rows and the rows to be columns. I have linked the values, but I need to make all the cells in the sheet absolute before I can transpose them. There are over 20,000 cells and I really don't want to hit the F4 key 20,000 times in the sheet. A replace would not work for this, as the data is too big. I have tried a copy and then paste special- paste link, but it only makes the reference absolute when one cell is copied at a time. Any ideas on making many cell references absolute at once? |
VBA is your only option.
Sub Absolute() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula(Cell.Formula, _ xlA1, xlA1, xlAbsolute) End If Next End Sub Gord Dibben Excel MVP On Thu, 19 May 2005 12:28:05 -0700, "Jamie A Miller" (donotspam) wrote: I set up a very large workbook. I have linked the formulas of one sheet to another, however, I need the columns to be rows and the rows to be columns. I have linked the values, but I need to make all the cells in the sheet absolute before I can transpose them. There are over 20,000 cells and I really don't want to hit the F4 key 20,000 times in the sheet. A replace would not work for this, as the data is too big. I have tried a copy and then paste special- paste link, but it only makes the reference absolute when one cell is copied at a time. Any ideas on making many cell references absolute at once? |
All times are GMT +1. The time now is 02:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com