how to lock all cell references in formulas?

[karhatsu]

Is there a possibility to lock all cell references in a range without knowing the cell contents? For example, I have three cells:
A1 = 3*B1/D$3
A2 = $C4
A3 = 125

I would like my macro to change them:
A1 = 3*$B$1/$D$3
A2 = $C$4
A3 = 125

The problem is that I don't what formulas or values the cells contain or if they are even just empty cells. I just know the range that requires the action.

[dannymking]

It will be possible, but it won't be that easy to code up...

you will need to loop around the cells in the range and then loop around the formula in each cell and scan the contents of the formula to check for cell reference...

Try something like this

Dim i As Long
Dim strTempVal As String
Dim StartFlag As Boolean
Dim NewForm As String
Do While ActiveCell.Formula <> ""
  NewForm = ""
  For i = 1 To Len(ActiveCell.Formula)
    If Mid(ActiveCell.Formula, i, 1) Like "[A-Za-z]" And Not StartFlag Then
      StartFlag = True
      strTempVal = "$" & Mid(ActiveCell.Formula, i, 1)
    ElseIf StartFlag And Mid(ActiveCell.Formula, i, 1) Like "[1-9]" Then
      strTempVal = strTempVal & "$" & Mid(ActiveCell.Formula, i, 1)
      NewForm = NewForm & strTempVal
    ElseIf StartFlag And Mid(ActiveCell.Formula, i, 1) Like "[!1-9]" Then
      NewForm = NewForm & Mid(ActiveCell.Formula, i - 1, 1) & Mid(ActiveCell.Formula, i, 1)
      StartFlag = False
    Else
      NewForm = NewForm & Mid(ActiveCell.Formula, i, 1)
    End If
  Next i
  StartFlag = False
  ActiveCell.Formula = NewForm
  ActiveCell.Offset(1, 0).Select
Loop

[JustinLabenne]

You can try this add-in. It allows for a selected range of formulas to be changed to a ref of your choosing.

Load it up, and it adds a menu item to you TOOLS menu