Macro problem for absolute reference

Dear big brother and sister,

I have a tricky task. I hope you can give me reply. appreciate your help.

I have the following macro. However, sometimes, it doesn't work, it may be because it doesn't applied on specific sheet. May I ask how can I modify the following macro to let it run on specific sheet only.

Secondly, as you go to sheet "before macro", after I use macro for Sub Absolute() (all cells selected are absolute reference", it returns to VALUE. May I know why?

Thanks

Sub Absolute()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

Sub AbsoluteRow()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub

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

Sub Relative()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlRelative)
End If
Next
End Sub

Thanks for your reply.

After i put the following code, it still doesn't work.
On the sheet "before macro", it still turn to value# (cell A 1to B7)
By the way, should i change sheets(1) of the macro to sheet(before macro)?

With Sheets(before macro) 'the number being the sheet you want to run code on

instead of

With Sheets(1) 'the number being the sheet you want to run code on

Sub Absolute()
Dim cell As Range
 
With Sheets(1) 'the number being the sheet you want to run code on
 
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
end with
 
End Sub

Moreover, the 2nd macro you write may be incomplete. what is the purpose of macro?

Sub Absolute()
Dim cell As Range
 
'Sheets(1).Select or Sheets(1).Activate The number being the sheet you want to run the code on.
 
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next

I tried to use the following macro, its work.
However, A1 to B7 still return to value# after I use macro (on sheet "before macro")

I believe I may need to modify the following macro....

Sub Absolute()
Dim cell As Range
 
With Sheets("before macro")
 
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End With
 
End Sub

Dear big brother and sister,

Can anyone help me? It seems the formula is right, but I don't know why the result will return to VALUE#. Before using macro, the answer is right. I would like to have macro for absolute reference

I search in google for two hours.

hi, ronlau, please check attachment, select cell or cells with formula and run code "Absolute"

hi, I'm glad it helped though the code was slightly changed from the your's one.

The code uses:

- For Each...Next Statement
- ConvertFormula method

See VB help file for details.

If you are happy with the offered help, please mark the thread as solved