Application or object defined error
in [Programming]
|
From: Project Mangler on 26 Mar 2010 06:54 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" <rspai9(a)gmail.com> wrote in message news:9aab4b9b-9698-4667-84cf-6dda14d15489(a)g1g2000pre.googlegroups.com... > 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
From: Raj on 26 Mar 2010 10:55 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" <dbl...(a)ntlworld.com> 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" <rsp...(a)gmail.com> wrote in message > > news:9aab4b9b-9698-4667-84cf-6dda14d15489(a)g1g2000pre.googlegroups.com... > > > 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
From: Raj on 26 Mar 2010 11:17 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" <dbl...(a)ntlworld.com> 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" <rsp...(a)gmail.com> wrote in message > > news:9aab4b9b-9698-4667-84cf-6dda14d15489(a)g1g2000pre.googlegroups.com... > > > 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
From: Project Mangler on 26 Mar 2010 11:30 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" <rspai9(a)gmail.com> wrote in message news:f558f9d9-a4e6-46de-ad73-f9c37755a786(a)x11g2000prb.googlegroups.com... 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" <dbl...(a)ntlworld.com> 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" <rsp...(a)gmail.com> wrote in message > > news:9aab4b9b-9698-4667-84cf-6dda14d15489(a)g1g2000pre.googlegroups.com... > > > 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
From: Raj on 26 Mar 2010 22:27 I want to remove the apostrophe but retain the = Regards, Raj On Mar 26, 8:30 pm, "Project Mangler" <dbl...(a)ntlworld.com> 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" <rsp...(a)gmail.com> wrote in message > > news:f558f9d9-a4e6-46de-ad73-f9c37755a786(a)x11g2000prb.googlegroups.com... > 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" <dbl...(a)ntlworld.com> 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" <rsp...(a)gmail.com> wrote in message > > >news:9aab4b9b-9698-4667-84cf-6dda14d15489(a)g1g2000pre.googlegroups.com... > > > > 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
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Picture Form Help Next: In what order do installed AddIns open? |