Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / November 2004

Tip: Looking for answers? Try searching our database.

Test for and Remove first character from ActiveCell

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Andy - 16 Nov 2004 23:57 GMT
I thought this would be easy to figure out, but I'm going in circles.

What would the code be to test the first character in the ActiveCell,
and if true, delete that character?

For example, say I want to remove the first character if it is a "z",
so if the original text is za1234, the new text becomes a1234.

This would be easy with a formula and a helper cell, but I'd like to
be able to do it with code.

TIA,

Andy
Jean-Guy Marcil - 17 Nov 2004 03:12 GMT
Andy was telling us:
Andy nous racontait que :

> I thought this would be easy to figure out, but I'm going in circles.
>
[quoted text clipped - 6 lines]
> This would be easy with a formula and a helper cell, but I'd like to
> be able to do it with code.

What is a "helper cell"?

To remove the "z" only in the active cell, try this:
'_______________________________________
Sub OneCell()

Dim myCell As Cell
Dim CellRge As Range

If Not Selection.Information(wdWithInTable) Then
   MsgBox "Selection must be in a table cell." _
       , vbExclamation, "Not in table"
   Exit Sub
End If

Set myCell = Selection.Cells(1)
With myCell
   Set CellRge = .Range
   With CellRge
       If LCase(.Characters(1)) = "z" Then
           .Characters(1).Delete
       End If
   End With
End With

End Sub
'_______________________________________

To check all cells in the selected table, try this:
'_______________________________________
Sub AllCell()

Dim TableRge As Range
Dim myCell As Cell
Dim CellRge As Range
Dim i As Long

If Not Selection.Information(wdWithInTable) Then
   MsgBox "Selection must be in a table cell." _
       , vbExclamation, "Not in table"
   Exit Sub
End If

Set TableRge = Selection.Tables(1).Range

With TableRge
   For i = 1 To .Cells.Count
       Set myCell = .Cells(i)
       With myCell
           Set CellRge = .Range
           With CellRge
               If LCase(.Characters(1)) = "z" Then
                   .Characters(1).Delete
               End If
           End With
       End With
   Next
End With

End Sub
'_______________________________________

By the way, this will remove "z" and "Z". If you want to be case specific,
remove LCase(...) from
   If LCase(.Characters(1)) = "z" Then
to obtain
   If .Characters(1) = "z" Then
and put the "z" or "Z"  after the "=".

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Andy - 17 Nov 2004 04:38 GMT
Oh no!  I put this message in the wrong section - I need an Excel
macro! (Hence the reference to a helper cell)

Sorry Jean-Guy

Merci,

Andy
Jean-Guy Marcil - 17 Nov 2004 06:10 GMT
Andy was telling us:
Andy nous racontait que :

> Oh no!  I put this message in the wrong section - I need an Excel
> macro! (Hence the reference to a helper cell)
>
> Sorry Jean-Guy

In this case, trythis:

'_______________________________________
Dim CellText As String

CellText = ActiveCell.Text

If Mid(CellText, 1, 1) = "z" Then
   CellText = Right(CellText, Len(CellText) - 1)
End If

ActiveCell.Value = CellText
'_______________________________________

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Andy - 17 Nov 2004 15:40 GMT
Thanks Jean-Guy.

I needed to think about which text I wanted, as opposed to what I
didn’t' want (delete).

Andy

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.