Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Scroll Bar missing "Control" tab in "Format Properties" dialog box | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |