Try this against a copy of your worksheet. You'll still have plenty to fix
manually, but it'll give you a little bit of a headstart:
Option Explicit
Sub testme()
Dim iStr As String
Dim oStr As String
Dim iCtr As Long
Dim Rng As Range
Dim myCell As Range
Dim myChar As String
Application.ScreenUpdating = False
Set Rng = Intersect(Selection, ActiveSheet.UsedRange)
For Each myCell In Rng.Cells
iStr = myCell.Value
If UCase(iStr) = iStr Then
'do nothing
Else
iStr = iStr & "." 'some dummy character
oStr = Left(iStr, 1)
For iCtr = 2 To Len(iStr) - 1
myChar = Mid(iStr, iCtr, 1)
If IsNumeric(myChar) Then
'do nothing
Else
If myChar = "." Then
'do nothing
Else
If myChar = UCase(myChar) Then
oStr = oStr & " "
End If
End If
End If
oStr = oStr & myChar
Next iCtr
myCell.Value = oStr
End If
Next myCell
Application.ScreenUpdating = True
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Short course:
Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Now go back to excel and test it out via:
select your range (ctrl-a (twice in xl2003) and then...
tools|macro|macros...
select the macro and click run.
==========
It's too late for this, but when I'm working on an important workbook, I'll save
intermediate copies:
MyImportantWorkBook_2005_07_12.xls
Every day I'll have at least one backup.
And if I'm doing lots of major stuff, I'll even have files like this:
MyImportantWorkBook_2005_07_12__10_04.xls
and
MyImportantWorkBook_2005_07_12__11_30.xls
and
MyImportantWorkBook_2005_07_12__15_45.xls
Disk space is cheap compared to the time it'll take to rebuild the workbook.
Good luck,
kelleychambers wrote:
I thought a macro could be written... however, my question is... how in
heck do I do it?
--
kelleychambers
------------------------------------------------------------------------
kelleychambers's Profile: http://www.excelforum.com/member.php...o&userid=25142
View this thread: http://www.excelforum.com/showthread...hreadid=386375
--
Dave Peterson