Changing cell references in a Range to Absolute

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good Morning. I found a post that provided several macros to force cell
references in a range to Absolute and tried it. Two problems - I can't find
the post to respond to and when I applied it via VBA it changed the last two
columns correctly, but the first column no shows the formula and I can't seem
to change it back to the values as so:
NAME AVG HDCP
='[Blues-27-Apr-06.xls]Sub_Scores'!$B$5 32.0 1

The code used was:
Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub

Any help will be appreciated...
 
You may be able to search Google Groups and find the original post and then
reply using Google.

But maybe this would work:

Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
With cell
If .HasFormula Then
.NumberFormat = "General"
.Formula = Application.ConvertFormula(.Formula, xlA1, _
xlA1, xlRelRowAbsColumn)
End If
End With
Next cell
End Sub



The said:
Good Morning. I found a post that provided several macros to force cell
references in a range to Absolute and tried it. Two problems - I can't find
the post to respond to and when I applied it via VBA it changed the last two
columns correctly, but the first column no shows the formula and I can't seem
to change it back to the values as so:
NAME AVG HDCP
='[Blues-27-Apr-06.xls]Sub_Scores'!$B$5 32.0 1

The code used was:
Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub

Any help will be appreciated...
Click to expand...
 
Back
Top