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

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Please: WHY do I get "Object missing" in line "Do While rng...." (https://www.excelbanter.com/excel-programming/329136-please-why-do-i-get-object-missing-line-do-while-rng.html)

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

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

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

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

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

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

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

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

http://www.vonixx.com

Done the corrections and more - but why ...

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" skrev i meddelandet
...
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

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