VBA question: Condense cell contents to first character

[Boodahbellie]

I have a column of first names that I want to condense down to the first
letter only (i.e. Chris becomes C). I have some code that I wrote, but it
doesn't work and I want to process every cell in that column that has a
value. Any help here would be appreciated...

Sub TrimText()
Dim FirstName As String

FirstName = ActiveCell.Text
If Len(FirstName) > 1 Then
FirstName = Left(ActiveCell.Text, 1)
ActiveCell.Text = FirstName
End If
End Sub

Also, is it possible to create macros that aren't imbedded in each XLS file?
I'd like to have several files that I can run this macro on.

Sean

 

[Guest]

Sub TrimText()
Dim FirstName As String
Dim r as Range, cell as Range
set r = ActiveCell.EntireColumn.specialCells(xlConstants,xlTextValues)
for each cell in r
FirstName = cell.Text
If Len(FirstName) > 1 Then
FirstName = Left(Cell.Text, 1)
Cell.Text = FirstName
End If
Next cell
End Sub

Record a macro and if you don't have a personal.xls workbook it will create
one if you choose to store the recorded macro in the personal workbook.

Now you can put any common code in your personal.xls workbook. It will be
opened each time excel is started and you will be able to select your macros
from the Tools=>Macro=Macros dialog and run them if they are in standard
modules and not marked as private.

 

[Boodahbellie]

Tom, I ran the macro and get "Runtime error '242': Object required"
What could I be doing wrong? I have the first cell of the target column
selected when I run the macro.

| Sub TrimText()
| Dim FirstName As String
| Dim r as Range, cell as Range
| set r = ActiveCell.EntireColumn.specialCells(xlConstants,xlTextValues)
| for each cell in r
| FirstName = cell.Text
| If Len(FirstName) > 1 Then
| FirstName = Left(Cell.Text, 1)
| Cell.Text = FirstName
| End If
| Next cell
| End Sub
|
| Record a macro and if you don't have a personal.xls workbook it will
create
| one if you choose to store the recorded macro in the personal workbook.
|
| Now you can put any common code in your personal.xls workbook. It will be
| opened each time excel is started and you will be able to select your
macros
| from the Tools=>Macro=Macros dialog and run them if they are in standard
| modules and not marked as private.
|
| --
| Regards,
| Tom Ogilvy
|
| "Boodahbellie" wrote:
|
| > I have a column of first names that I want to condense down to the first
| > letter only (i.e. Chris becomes C). I have some code that I wrote, but
it
| > doesn't work and I want to process every cell in that column that has a
| > value. Any help here would be appreciated...
| >
| > Sub TrimText()
| > Dim FirstName As String
| >
| > FirstName = ActiveCell.Text
| > If Len(FirstName) > 1 Then
| > FirstName = Left(ActiveCell.Text, 1)
| > ActiveCell.Text = FirstName
| > End If
| > End Sub
| >
| > Also, is it possible to create macros that aren't imbedded in each XLS
file?
| > I'd like to have several files that I can run this macro on.
| >
| > Sean
| >
| >
| >

 

[Guest]

the TEXT property is a read only property. I just replaced the
ActiveCell.Text by deleting "Active". I didn't notice you were assigning the
value to the Text property.

This revision worked for me.

Sub TrimText()
Dim FirstName As String
Dim r As Range, cell As Range
Set r = ActiveCell.EntireColumn.SpecialCells(xlConstants, xlTextValues)
For Each cell In r
FirstName = cell.Text
If Len(FirstName) > 1 Then
FirstName = Left(cell.Text, 1)
cell.Value = FirstName
End If
Next cell
End Sub