inserting $ at every cell mentioned in a formula

V

VILLABILLA

Hi!

I have several long formula's in different cells and I would like t
add a $ sign to every cell mentioned in those formula's. My question i
how can I do this in an easy way, manually filling in these signs wil
take ages, please help!

Regards
 
J

Jan Karel Pieterse

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!
Click to expand...

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
 
D

David McRitchie

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/IndirectBlanksConverFormula.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
 
Top