Please: WHY do I get "Object missing" in line "Do While rng...."

[Maria J-son]

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

 

[Edwin Tam]

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
(e-mail address removed)
http://www.vonixx.com

 

[Edwin Tam]

Sorry, I found some errors in other parts of your code.
The bottom part of the code (the DO-LOOP area) should be replaced with:

NewString = rng.Formula 'Set string
Do While InStr(NewString, "$") <> 0
PositionDollar = InStr(NewString, "$")
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
Loop
If rng.Formula <> NewString Then rng.Formula = NewString


Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[Maria J-son]

Thank you Edwin and Peter,
Yeas, I found I used instr instead of mid also. Now I'm almost there, but
still I have something wrong but what? I test it on following cell formulas.
Example formulas in A1:A6:



=Sheet2!A1 'Should become =Sheet2!$A$1
=15*B$2 'Should become =15*Sheet1!$B$2
=15*$B1-A2 'Should become =15*Sheet1!$B$1-Sheet1!$A$2
=B4*56/(3+4)*$C3 'Should become =Sheet1!$B$4*56/(3+4)*Sheet1!$C$3
=F2*Sheet2!A$2 'Should become =Sheet1!$F$2*Sheet2!$A$2
=F$3 'Should become =Sheet1!$F$3


!! The code doesn't insert the Sheet1! where it should. Please, can you or
anybody find out were it is wrong?

/Regards



The code:



Sub AbsoluteReferenceSameSheetNew()

'========= 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
Dim NewString As String
Dim PositionDollar As Long
Dim RightPart As String
Dim LeftPart As String
Dim samedollar As Long
NewString = rng.Formula 'Set string
Do While InStr(NewString, "$") <> 0
PositionDollar = InStr(NewString, "$")

If SameDollarCount = PositionDollar Then ' End loop if it
find same caracter twice
GoTo OutLoop
End If
SameDollarCount = PositionDollar



If Not Mid(NewString, InStr(1, NewString, "$") - 1, 1) = "!" Then
If Not Mid(NewString, InStr(1, NewString, "$") + 2, 1) = "$"
Then
LeftPart = Left(NewString, PositionDollar)
RightPart = Right(NewString, PositionDollar)
NewString = LeftPart & ActiveSheet.Name & "!" & RightPart
End If
End If
Loop


OutLoop:
If rng.Formula <> NewString Then rng.Formula = NewString


endpart:
Debug.Print NewString

Next rng

End Sub

Sorry, I found some errors in other parts of your code.
The bottom part of the code (the DO-LOOP area) should be replaced with:

NewString = rng.Formula 'Set string
Do While InStr(NewString, "$") <> 0
PositionDollar = InStr(NewString, "$")
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
Loop
If rng.Formula <> NewString Then rng.Formula = NewString


Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

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