Prev: Picture Form Help
Next: In what order do installed AddIns open?
From: Project Mangler on 27 Mar 2010 08:35
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" <rspai9(a)gmail.com> wrote in message
news:e44e445f-56fd-4693-843c-bfd5d2aed3e6(a)n20g2000prh.googlegroups.com...
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  | 
Pages: 1 2 3
Prev: Picture Form Help
Next: In what order do installed AddIns open?