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

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" <(E-Mail Removed)> wrote in message
news:9aab4b9b-9698-4667-84cf-(E-Mail Removed)...
> 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

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" <d...@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" <rsp...@gmail.com> wrote in message
>
> news:9aab4b9b-9698-4667-84cf-(E-Mail Removed)...
>
> > 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

Do you have a "Sheet1" tab name in "Thisworkbook"?



--
Regards
Dave Hawley
www.ozgrid.com
"Raj" <(E-Mail Removed)> wrote in message
news:12a52095-e99c-4f7b-a89c-(E-Mail Removed)...
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" <d...@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" <rsp...@gmail.com> wrote in message
>
> news:9aab4b9b-9698-4667-84cf-(E-Mail Removed)...
>
> > 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

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" <d...@ozgrid.com> wrote:
> Do you have a "Sheet1" tab name in "Thisworkbook"?
>
> --
> Regards
> Dave Hawleywww.ozgrid.com"Raj" <rsp...@gmail.com> wrote in message
>
> news:12a52095-e99c-4f7b-a89c-(E-Mail Removed)...
> 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" <d...@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" <rsp...@gmail.com> wrote in message
>
> >news:9aab4b9b-9698-4667-84cf-(E-Mail Removed)...
>
> > > 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

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" <(E-Mail Removed)> wrote in message
news:9aab4b9b-9698-4667-84cf-(E-Mail Removed)...
> 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

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...@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...@gmail.com> wrote in message
>
> news:9aab4b9b-9698-4667-84cf-(E-Mail Removed)...
>
> > 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

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...@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...@gmail.com> wrote in message
>
> news:9aab4b9b-9698-4667-84cf-(E-Mail Removed)...
>
> > 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

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" <(E-Mail Removed)> wrote in message
news:f558f9d9-a4e6-46de-ad73-(E-Mail Removed)...
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...@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...@gmail.com> wrote in message
>
> news:9aab4b9b-9698-4667-84cf-(E-Mail Removed)...
>
> > 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

I want to remove the apostrophe but retain the =

Regards,
Raj


On Mar 26, 8:30*pm, "Project Mangler" <dbl...@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...@gmail.com> wrote in message
>
> news:f558f9d9-a4e6-46de-ad73-(E-Mail Removed)...
> 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...@ntlworld.com> wrote:
>
> > Raj,
>
> > I can't duplicate your error here, the code works OK if I try to detecta
> > 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...@gmail.com> wrote in message
>
> >news:9aab4b9b-9698-4667-84cf-(E-Mail Removed)...
>
> > > 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