If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
inserting $ at every cell mentioned in a formula
Hi!
I have several long formula's in different cells and I would like to add a $ sign to every cell mentioned in those formula's. My question is how can I do this in an easy way, manually filling in these signs will take ages, please help! Regards, --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
inserting $ at every cell mentioned in a formula
Hi Villabilla,
I have several long formula's in different cells and I would like to add a $ sign to every cell mentioned in those formula's. My question is how can I do this in an easy way, manually filling in these signs will take ages, please help! Maybe with this macro: Sub ConvertToAbsolute() Dim oCell As Range For Each oCell In ActiveSheet.UsedRange.Cells If Left(oCell.Formula, 1) = "=" Then oCell.Formula = Application.ConvertFormula( _ oCell.Formula, Application.ReferenceStyle, , True) End If Next End Sub Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com |
#3
|
|||
|
|||
inserting $ at every cell mentioned in a formula
Hi Villabilla,
I probably would have used selection and specialcells for formulas if I had written them (today), but I'd certainly use selection instead of usedrange in Jan Karel's suggestion for a lot more flexibility. or ... Here are a couple of macros by Bernie Deitrick, convert to absolute references, convert to non absolute references Sub ConvertToAbsoluteReferences() 'Bernie Deitrick, Email 1999-09-10 Dim myCell As Range For Each myCell In Selection If myCell.HasFormula Then myCell.Formula = Application.ConvertFormula(myCell.Formula, _ xlA1, xlA1, xlAbsolute) End If Next myCell End Sub Sub ConvertToNonAbsoluteReferences() 'Bernie Deitrick, Email 1999-09-10 Dim myCell As Range For Each myCell In Selection If myCell.HasFormula Then myCell.Formula = Application.ConvertFormula(myCell.Formula, _ xlA1, xlA1, xlRelative) End If Next myCell End Sub As you might infer, you could adapt additional macros to use xlAbsolute, xlAbsRowRelColumn, xlRelRowAbsColumn, or xlRelative If you wanted to adapt formulas so you could use the fill handle. http://www.mvps.org/dmcritchie/excel/fillhand.htm you can also use ConvertFormula to change R1C1 addressing to A1 addressing. Possibly this is one instance where the input box at the beginning to ask you what type of conversion you wanted would be tolerable: http://www.ozgrid.com/News/IndirectB...verFormula.htm (not easy to view with backgroun image, suggest using Mozilla) Since this is not the excel.programming newsgroup -- If not familiar with installing and using macros see Getting Started with Macros http://www.mvps.org/dmcritchie/excel/getstarted.htm If you have a lot of trouble reading a webpage because of background images, bad color choices, bad CSS style sheets you can perhaps use a bookmarklet. http://www.mvps.org/dmcritchie/ie/bookmarklets.htm -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Jan Karel Pieterse" wrote in message Hi Villabilla, I have several long formula's in different cells and I would like to add a $ sign to every cell mentioned in those formula's. My question is how can I do this in an easy way, manually filling in these signs will take ages, please help! Maybe with this macro: Sub ConvertToAbsolute() Dim oCell As Range For Each oCell In ActiveSheet.UsedRange.Cells If Left(oCell.Formula, 1) = "=" Then oCell.Formula = Application.ConvertFormula( _ oCell.Formula, Application.ReferenceStyle, , True) End If Next End Sub Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com |
Thread Tools | |
Display Modes | |
|
|