Replace the line with:
Do While InStr(rng.Formula, "$") < 0


BTW, you may want also to declare two of the variables being used:
Dim LeftPart As String, RightPart As String


Edwin Tam

http://www.vonixx.com



"Maria J-son" wrote:

Hi,

WHY do I get "Object Missing" error in line
" Do While rng.Formula.Find(What:="$") < 0"

The first part of the sub is ok standalone, but the second part where I try
to add the [activesheet.name]! in front of the references to the
activesheet make Object Missing"

/Regards

Sub AbsoluteReferenceSameSheet()
'========= Convert to absolute references
Dim rng As Range
For Each rng In Range("A1:A5").SpecialCells(xlCellTypeFormulas)
With rng
If .HasArray Then
.FormulaArray =
Application.ConvertFormula(.FormulaArray, xlA1, xlA1, xlAbsolute)
Else
.Formula = Application.ConvertFormula(.Formula, xlA1,
xlA1, xlAbsolute)
End If
End With

'========= Insert ActiveSheetname Where Missing for cell references in
activesheet
Dim NewString As String
Dim PositionDollar As Long

NewString = rng.Formula 'Set string
Do While rng.Formula.Find(What:="$") < 0
With rng.Formula
PositionDollar = .Find(What:="$")
If Not InStr(1, NewString, "$") - 1 = "!" Then
If Not InStr(1, NewString, "$") + 2 = "$" Then
LeftPart = Left(NewString, PositionDollar)
RightPart = Right(NewString, PositionDollar)
NewString = LeftPart & ActiveSheet.Name & "!" &
RightPart
End If
End If
End With
Loop

endpart:
Debug.Print NewString

Next rng

End Sub