Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object defined error
Hi,
When I run the following code, I am getting the Application or object defined error. The second line is a single line in my code. The VBE highlights the portion after "Then" in the second line. Sub Cleancolumn1() For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 14).End(xlUp).Row If Left(Cells(i, 14), 1) = "'" Then ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), Len(Cells(i, 14)) - 1) Next i End Sub What is going wrong? Thanks in advance for the help. Regards, Raj |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object defined error
Hi Raj
Do you mean? Sub Cleancolumn1() With ThisWorkbook.Worksheets("Sheet1") For i = 1 To .Cells(Rows.Count, 14).End(xlUp).Row If Left(.Cells(i, 14), 1) = "'" Then .Cells(i, 14) = Right(.Cells(i, 14), Len(.Cells(i, 14)) - 1) End If Next i End With End Sub -- Regards Dave Hawley www.ozgrid.com "Raj" wrote in message ... Hi, When I run the following code, I am getting the Application or object defined error. The second line is a single line in my code. The VBE highlights the portion after "Then" in the second line. Sub Cleancolumn1() For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 14).End(xlUp).Row If Left(Cells(i, 14), 1) = "'" Then ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), Len(Cells(i, 14)) - 1) Next i End Sub What is going wrong? Thanks in advance for the help. Regards, Raj |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object defined error
Yes. But the error again occurred again. This time the line above the
End If was highlighted in yellow with the same error code displayed. Regards, Raj On Mar 26, 11:35*am, "ozgrid.com" wrote: Hi Raj Do you mean? Sub Cleancolumn1() * * With ThisWorkbook.Worksheets("Sheet1") * * * * For i = 1 To .Cells(Rows.Count, 14).End(xlUp).Row * * * * * * If Left(.Cells(i, 14), 1) = "'" Then * * * * * * * * .Cells(i, 14) = Right(.Cells(i, 14), Len(.Cells(i, 14)) - 1) * * * * * * End If * * * * Next i * * End With End Sub -- Regards Dave Hawleywww.ozgrid.com"Raj" wrote in message ... Hi, When I run the following code, I am getting the Application or object defined error. The second line is a single line in my code. The VBE highlights the portion after "Then" in the second line. Sub Cleancolumn1() For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 14).End(xlUp).Row If Left(Cells(i, 14), 1) = "'" Then ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), Len(Cells(i, 14)) - 1) Next i End Sub What is going wrong? Thanks in advance for the help. Regards, Raj |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object defined error
Do you have a "Sheet1" tab name in "Thisworkbook"?
-- Regards Dave Hawley www.ozgrid.com "Raj" wrote in message ... Yes. But the error again occurred again. This time the line above the End If was highlighted in yellow with the same error code displayed. Regards, Raj On Mar 26, 11:35 am, "ozgrid.com" wrote: Hi Raj Do you mean? Sub Cleancolumn1() With ThisWorkbook.Worksheets("Sheet1") For i = 1 To .Cells(Rows.Count, 14).End(xlUp).Row If Left(.Cells(i, 14), 1) = "'" Then .Cells(i, 14) = Right(.Cells(i, 14), Len(.Cells(i, 14)) - 1) End If Next i End With End Sub -- Regards Dave Hawleywww.ozgrid.com"Raj" wrote in message ... Hi, When I run the following code, I am getting the Application or object defined error. The second line is a single line in my code. The VBE highlights the portion after "Then" in the second line. Sub Cleancolumn1() For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 14).End(xlUp).Row If Left(Cells(i, 14), 1) = "'" Then ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), Len(Cells(i, 14)) - 1) Next i End Sub What is going wrong? Thanks in advance for the help. Regards, Raj |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object defined error
Thanks, Dave, for the quick response. I do have a Sheet1 in the
workbook. To help trouble-shoot the problem I rewrote the code as follows: Sub cleancolumn3() Dim ws As Worksheet Dim rsplen As Long Dim rspstring As String Set ws = ThisWorkbook.Worksheets("Sheet1") For i = 1 To ws.Cells(Rows.Count, 14).End(xlUp).Row If Left(ws.Cells(i, 14), 1) = "'" Then rsplen = Len(ws.Cells(i, 14)) - 1: rspstring = Right(ws.Cells(i, 14), rsplen): ws.Cells(i, 14) = rspstring Next i End Sub The last statement in the If line viz. "ws.Cells(i,14) = rspstring" is highlighted when I debug the Application Defined or Object Defined error. The problem seems to be assigning the string to the cell value. Any other way to do this? Regards, Raj On Mar 26, 1:25*pm, "ozgrid.com" wrote: Do you have a "Sheet1" tab name in "Thisworkbook"? -- Regards Dave Hawleywww.ozgrid.com"Raj" wrote in message ... Yes. But *the error again occurred again. This time the line above the End If was highlighted in yellow with the same error code displayed. Regards, Raj On Mar 26, 11:35 am, "ozgrid.com" wrote: Hi Raj Do you mean? Sub Cleancolumn1() With ThisWorkbook.Worksheets("Sheet1") For i = 1 To .Cells(Rows.Count, 14).End(xlUp).Row If Left(.Cells(i, 14), 1) = "'" Then .Cells(i, 14) = Right(.Cells(i, 14), Len(.Cells(i, 14)) - 1) End If Next i End With End Sub -- Regards Dave Hawleywww.ozgrid.com"Raj" wrote in message ... Hi, When I run the following code, I am getting the Application or object defined error. The second line is a single line in my code. The VBE highlights the portion after "Then" in the second line. Sub Cleancolumn1() For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 14).End(xlUp).Row If Left(Cells(i, 14), 1) = "'" Then ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), Len(Cells(i, 14)) - 1) Next i End Sub What is going wrong? Thanks in advance for the help. Regards, Raj |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object defined error
Raj,
I can't duplicate your error here, the code works OK if I try to detect a haracter other than "'" . I'm wondering how you are going to detect a single quote at the start of a string? e.g. If Left(Cells(i, 4), 1) = "'" Then MsgBox "Apostrophe!" Else MsgBox "No Apostrophe!" End If The above line copied from your post gives me No Apostrophe. In a cell containing the string 'Length I get a Len() of 6. DB "Raj" wrote in message ... Hi, When I run the following code, I am getting the Application or object defined error. The second line is a single line in my code. The VBE highlights the portion after "Then" in the second line. Sub Cleancolumn1() For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 14).End(xlUp).Row If Left(Cells(i, 14), 1) = "'" Then ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), Len(Cells(i, 14)) - 1) Next i End Sub What is going wrong? Thanks in advance for the help. Regards, Raj |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object defined error
Maybe this information will throw light on the underlying problem and
also help me with a solution: Column 14 which is being cleaned has some cells beginning with an apostrophe and an = sign. eg. '=KKRRNN This has been done obviously because without the apostrophe Excel treats the string as a formula and shows a Name error because it does not understand the gibberish following the equal to sign. The code was written to remove the apostrophe where one existed. I am wondering whether the problem is occurring because string without the apostrophe becomes a formula. Please examine and also let me know any other way to get rid of the leading apostrophe in a string in a cell. Thanks in Advance. Regards, Raj On Mar 26, 3:54*pm, "Project Mangler" wrote: Raj, I can't duplicate your error here, the code works OK if I try to detect a haracter other than "'" . I'm wondering how you are going to detect a single quote at the start of a string? e.g. If Left(Cells(i, 4), 1) = "'" Then MsgBox "Apostrophe!" Else MsgBox "No Apostrophe!" End If The above line copied from your post gives me No Apostrophe. In a cell containing the string 'Length I get a Len() of 6. DB "Raj" wrote in message ... Hi, When I run the following code, I am getting the Application or object defined error. The second line is a single line in my code. The VBE highlights the portion after "Then" in the second line. Sub Cleancolumn1() For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 14).End(xlUp).Row If Left(Cells(i, 14), 1) = "'" Then ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), Len(Cells(i, 14)) - 1) Next i End Sub What is going wrong? Thanks in advance for the help. Regards, Raj |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object defined error
Maybe this information will throw light on the underlying problem and
also help me with a solution: Column 14 which is being cleaned has some cells beginning with an apostrophe and an = sign. eg. '=KKRRNN This has been done obviously because without the apostrophe Excel treats the string as a formula and shows a Name error because it does not understand the gibberish following the equal to sign. The code was written to remove the apostrophe where one existed. I am wondering whether the problem is occurring because string without the apostrophe becomes a formula. Please examine and also let me know any other way to get rid of the leading apostrophe in a string in a cell. Thanks in Advance. Regards, Raj On Mar 26, 3:54*pm, "Project Mangler" wrote: Raj, I can't duplicate your error here, the code works OK if I try to detect a haracter other than "'" . I'm wondering how you are going to detect a single quote at the start of a string? e.g. If Left(Cells(i, 4), 1) = "'" Then MsgBox "Apostrophe!" Else MsgBox "No Apostrophe!" End If The above line copied from your post gives me No Apostrophe. In a cell containing the string 'Length I get a Len() of 6. DB "Raj" wrote in message ... Hi, When I run the following code, I am getting the Application or object defined error. The second line is a single line in my code. The VBE highlights the portion after "Then" in the second line. Sub Cleancolumn1() For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 14).End(xlUp).Row If Left(Cells(i, 14), 1) = "'" Then ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), Len(Cells(i, 14)) - 1) Next i End Sub What is going wrong? Thanks in advance for the help. Regards, Raj |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object defined error
Raj,
I see the point of the single quote. If you remove it and paste the truncated string back into the cell you will get the name error again? Is this what you are trying to achieve or should you remove the = as well? DB "Raj" wrote in message ... Maybe this information will throw light on the underlying problem and also help me with a solution: Column 14 which is being cleaned has some cells beginning with an apostrophe and an = sign. eg. '=KKRRNN This has been done obviously because without the apostrophe Excel treats the string as a formula and shows a Name error because it does not understand the gibberish following the equal to sign. The code was written to remove the apostrophe where one existed. I am wondering whether the problem is occurring because string without the apostrophe becomes a formula. Please examine and also let me know any other way to get rid of the leading apostrophe in a string in a cell. Thanks in Advance. Regards, Raj On Mar 26, 3:54 pm, "Project Mangler" wrote: Raj, I can't duplicate your error here, the code works OK if I try to detect a haracter other than "'" . I'm wondering how you are going to detect a single quote at the start of a string? e.g. If Left(Cells(i, 4), 1) = "'" Then MsgBox "Apostrophe!" Else MsgBox "No Apostrophe!" End If The above line copied from your post gives me No Apostrophe. In a cell containing the string 'Length I get a Len() of 6. DB "Raj" wrote in message ... Hi, When I run the following code, I am getting the Application or object defined error. The second line is a single line in my code. The VBE highlights the portion after "Then" in the second line. Sub Cleancolumn1() For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 14).End(xlUp).Row If Left(Cells(i, 14), 1) = "'" Then ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), Len(Cells(i, 14)) - 1) Next i End Sub What is going wrong? Thanks in advance for the help. Regards, Raj |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object defined error
I want to remove the apostrophe but retain the =
Regards, Raj On Mar 26, 8:30*pm, "Project Mangler" wrote: Raj, I see the point of the single quote. If you remove it and paste the truncated string back into the cell you will get the name error again? Is this what *you are trying to achieve or should you remove the = as well? DB "Raj" wrote in message ... Maybe this information will throw light on the underlying problem and also help me with a solution: Column 14 which is being cleaned has some cells beginning with *an apostrophe and an = sign. eg. '=KKRRNN This has been done obviously because without the apostrophe Excel treats the string as a formula and shows a Name error because it does not understand the gibberish following the equal to sign. The code was written to remove the apostrophe where one existed. I am wondering whether the problem is occurring because string without the apostrophe becomes a formula. Please examine and also let me know any other way to get rid of the leading apostrophe in a string in a cell. Thanks in Advance. Regards, Raj On Mar 26, 3:54 pm, "Project Mangler" wrote: Raj, I can't duplicate your error here, the code works OK if I try to detect a haracter other than "'" . I'm wondering how you are going to detect a single quote at the start of a string? e.g. If Left(Cells(i, 4), 1) = "'" Then MsgBox "Apostrophe!" Else MsgBox "No Apostrophe!" End If The above line copied from your post gives me No Apostrophe. In a cell containing the string 'Length I get a Len() of 6. DB "Raj" wrote in message ... Hi, When I run the following code, I am getting the Application or object defined error. The second line is a single line in my code. The VBE highlights the portion after "Then" in the second line. Sub Cleancolumn1() For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 14).End(xlUp).Row If Left(Cells(i, 14), 1) = "'" Then ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), Len(Cells(i, 14)) - 1) Next i End Sub What is going wrong? Thanks in advance for the help. Regards, Raj |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object defined error
Raj,
I'm till not sure what your target cell should look like but here are two ideas: Remove the single quote, retain the = but accept #NAME errors: Sub Cleancolumn1() For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells _ (Rows.Count, 14).End(xlUp).Row Cells(i, 14) = Cells(i, 14).Value Next i End Sub Remove the single quote, retain =, display without name error: This means for a string like '123 yiu will end up with ="123" I have no idea if this is acceptable. Sub Cleancolumn2() Dim A As String Dim B As Long For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells _ (Rows.Count, 14).End(xlUp).Row A = Cells(i, 14) B = Len(A) Select Case B Case 0 'do nothing Case 1 'paste whatever is there Cells(i, 14) = Cells(i, 14).Value Case Is = 2 If Left(Cells(i, 14), 1) = "=" Then A = Right(Cells(i, 14), B - 1) ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = "=" & A ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = "=" & Chr(34) & A & Chr(34) Else Cells(i, 14) = Cells(i, 14).Value End If End Select Next i End Sub "Raj" wrote in message ... I want to remove the apostrophe but retain the = Regards, Raj On Mar 26, 8:30 pm, "Project Mangler" wrote: Raj, I see the point of the single quote. If you remove it and paste the truncated string back into the cell you will get the name error again? Is this what you are trying to achieve or should you remove the = as well? DB "Raj" wrote in message ... Maybe this information will throw light on the underlying problem and also help me with a solution: Column 14 which is being cleaned has some cells beginning with an apostrophe and an = sign. eg. '=KKRRNN This has been done obviously because without the apostrophe Excel treats the string as a formula and shows a Name error because it does not understand the gibberish following the equal to sign. The code was written to remove the apostrophe where one existed. I am wondering whether the problem is occurring because string without the apostrophe becomes a formula. Please examine and also let me know any other way to get rid of the leading apostrophe in a string in a cell. Thanks in Advance. Regards, Raj On Mar 26, 3:54 pm, "Project Mangler" wrote: Raj, I can't duplicate your error here, the code works OK if I try to detect a haracter other than "'" . I'm wondering how you are going to detect a single quote at the start of a string? e.g. If Left(Cells(i, 4), 1) = "'" Then MsgBox "Apostrophe!" Else MsgBox "No Apostrophe!" End If The above line copied from your post gives me No Apostrophe. In a cell containing the string 'Length I get a Len() of 6. DB "Raj" wrote in message ... Hi, When I run the following code, I am getting the Application or object defined error. The second line is a single line in my code. The VBE highlights the portion after "Then" in the second line. Sub Cleancolumn1() For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 14).End(xlUp).Row If Left(Cells(i, 14), 1) = "'" Then ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), Len(Cells(i, 14)) - 1) Next i End Sub What is going wrong? Thanks in advance for the help. Regards, Raj |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run Time Error 1004 Application-defined or object-defined error forsimple loop? Need help with what's wrong? | Excel Programming | |||
Export a chart in a GIF file. Run-time error '1004': Application-defined or object-defined error; | Excel Programming | |||
Copying A Range of Cells From one Sheet to Another - Error Application Defined or Object Defined | Excel Programming | |||
error: ActiveCell.Offset(0, -1).Select = Application-defined or object-defined error | Excel Programming | |||
Macro Run-time Error 1004 Application Defined or Object Defined Error | Excel Programming |