Copy referral $ sign to other cells

[Billy]

Hi, I have a scenario here but I am not sure how to do it.
I have a spreadsheet here. For example:
The cell 1 value is =$A1$b2 and the cell value for other
cells are other misc formulas similar to cell 1 without
the dollar sign..say example: =B6O5
How can I copy the $ referral sign and paste it to other
cells and made everything into =$X$Y format without
having to manually add it on every cell?

Thanks

Billy

 

[Tom Ogilvy]

=$A1$B2

returns the error #Name

even if it was a legitimate range reference

=$A1:$B2

also returns an error. (#Value)

What are you really trying to do.

Perhaps you can figure it out on your own by looking at Excel VBA help for
the

Application.ConvertFormula function .

 

[Guest]

You know.. it was just an example. I just want to add the
referral $ sign on the rest of the signs without erasing
the data in the cells. Copy and paste wouldn't work becoz
each cell is referring to different cells. Thanks

 

[Tom Ogilvy]

Dim rng as Range, cell as Range
set rng = Cells.SpecialCells(xlFormulas)
for each cell in rng
if not cell.DirectPrecedents is Nothing then
sForm = cell.Formula
sForm = application.ConvertFormula(sForm,xlA1,,xlRelRowAbsColumn)
cell.Formula = sform
end if
Next

Test this on a **copy** of your worksheet.

 

[Guest]

Thanks.. but I am not sure how it works. Should I copy
the entire formula below and where should I paste it to?
I appreciate your input. Thanks again.

 

[Tom Ogilvy]

Sub changeFormulas()
Dim rng as Range, cell as Range
set rng = Cells.SpecialCells(xlFormulas)
for each cell in rng
if not cell.DirectPrecedents is Nothing then
sForm = cell.Formula
sForm = application.ConvertFormula(sForm,xlA1,,xlRelRowAbsColumn)
cell.Formula = sform
end if
Next
end Sub

go into the VBE (Alt+F11). Do insert=>Module. Paste in the code. Go back
to excel. Make the copy of your sheet the active sheet.

go to Tools=>Macro=>Macros, select the macro named Changeformulas and select
Run

Hopefully it will do what you want.