Macro problem for absolute reference

**ronlau123** · 06-16-2011, 04:35 PM

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

**realniceguy5000** · 06-17-2011, 10:31 AM

Hi,

I was only just giving you examples to try not complete scripts you only need to try one of the ways to see if it works. After the Dim statement try one of these methods:

Sheets(1).Select 
 
OR 
 
Sheets("THE NAME OF THE SHEET YOU WANT THE CODE TO RUN ON").Select
 
Or
 
Sheets(1).Activate
 
or
 
With Sheets("THE NAME OF THE SHEET YOU WANT THE CODE TO RUN ON")
 
And place End With After the current End With in the script.

.

Also

Sheets(before macro)

.
Should be like this:

Sheets("before macro")

The number I was talking about is the count of the worksheets you want to run your code on. For example you open a new workbook you have 3 sheet tabs with the names sheet1 ,sheet2 and sheet3 Say you want to run code on sheet2 you could use sheets("sheet2") by the name of the worksheet or sheets(2) by the index number placement of the worksheet.

You dont really need to activate or select a worksheet to make a script work but it may help you to see what your script is doing when you step through it.

Hope that helps some.

**ronlau123** · 06-19-2011, 02:27 AM

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