Hi

The problem I'm currently having is that I want my macro to capitalise a letter that comes after the end of any sentence - at the minute it only capitalises a letter if it comes after a full stop (period for any americans reading!). Essentially what I want is to turn

this sentence isn't written properly. why not? people can't be bothered to press shift! it's annoying.

into

This sentence isn't written properly. Why not? People can't be bothered to press shift! It's annoying.

The part I've highlighted in red is the part that currently puts a capital letter after a full stop. I'm new to VBA but I'm now thinking that as well as needing ? and ! adding in, the way it's been done is definitely not the most efficient? Am I right? I've found a couple of things similar on other forums e.g:

http://stackoverflow.com/questions/1...case-using-vba

http://www.vbforums.com/showthread.p...-each-sentence

But nothing seems to do exactly what I want which is to change this text in the original cell. I've tried using trial and error to fix some codes that do similar things but nothing seems to work - I can get them to run eventually without an error but they either just delete all the text out or do nothing. 

Sub Sentence()

' Sentence Macro

Let Bob = Range("A1")
    Dim cell    As Range
    Dim asSent() As String
    Dim asWord() As String
    Dim i       As Long
    Dim j       As Long
    Dim str As String
    Dim nAscii As Integer
    Dim LR As Long
    Dim Para As ParagraphFormat2
     
    For Each cell In Selection
        With cell
            cell = LCase(cell)
        End With
    Next

For Each cell In Selection
        With cell
            Selection.Replace What:="e.g.", Replacement:="xcv123", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        End With
Next

For Each cell In Selection
        With cell
            Selection.Replace What:="i.e.", Replacement:="xcv246", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        End With
Next
    
    For Each cell In Selection
        With cell
            If Len(.Text) And Not (.HasFormula) Then
                asSent = Split(.Text, ".")
                For i = 0 To UBound(asSent)
                    If Len(Trim(asSent(i))) Then
                        asWord = Split(asSent(i), " ")
                        For j = 0 To UBound(asWord)
                            If Len(asWord(j)) Then
                                asWord(j) = StrConv(asWord(j), vbProperCase)
                                Exit For
                            End If
                        Next j
                        asSent(i) = Join(asWord, " ")
                    End If
                Next i
                .Value = Join(asSent, ".")
            End If
     
        With cell
            cell = WorksheetFunction.Trim(cell)
        End With
   End With
Next
    
    For Each cell In Selection
        With cell
            Selection.Replace What:=" i ", Replacement:=" I ", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        End With
    Next
    
    For Each cell In Selection
        With cell
            Selection.Replace What:="xcv123", Replacement:="e.g.", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        End With
    Next
    
    For Each cell In Selection
        With cell
            Selection.Replace What:="xcv246", Replacement:="i.e.", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        End With
    Next
    
    For Each cell In Selection
        With cell
            If Right(.Value, 1) <> "." Then .Value = .Value & "."
        End With
    Next
        
End Sub
Any help would be really appreciated!

I tried to replace it with the code from the stackoverflow forum removing the bit about the message box

Dim objRegex As Object
    Dim objRegMC As Object
    Dim objRegM As Object
    Dim strIn As String
    Dim PropserCaps As String

For Each cell In Selection
With cell
    Set objRegex = CreateObject("vbscript.regexp")
    strIn = LCase(strIn)
    With objRegex
        .Global = True
        .ignoreCase = True
        .Pattern = "(^|[\.\?\!\rzt]\s?)([a-z])"
    If .test(strIn) Then
        Set objRegMC = .Execute(strIn)
        For Each objRegM In objRegMC
            Mid$(strIn, objRegM.firstindex + 1, objRegM.Length) = UCase$(objRegM)
        Next
    End If
    End With
End With
Next
But currently it just doesn't do anything.

Thanks