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

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

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
[email protected]
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
>
>
>

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
[email protected]
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
>
>
>

In addition to what Edwin wrote:

Maria, you applied the Find method to a string (rng.Formula) but you only
may apply it to an object (e.g. a range, such as rng).

Greetings,
Peter

"Edwin Tam" wrote:

> 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
> [email protected]
> http://www.vonixx.com

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







"Edwin Tam" <[email protected]> skrev i meddelandet
news:[email protected]...
> 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
> [email protected]
> 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
>>
>>
>>