Automatically Change Uppercase to Lowercase

[ir121973]

I wonder whether someone may be able to help me please.

I am using the code below to copy and paste data between sheets.

Sub CopyMonuments()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Set ws1 = Sheets("Input")
Set ws2 = Sheets("Output")
On Error Resume Next
Set rng1 = ws1.Range(ws1.[b4], ws1.Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlConstants)
On Error GoTo 0
If rng1 Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set rng2 = ws2.[c4]
rng1.Copy
rng2.PasteSpecial xlPasteValues
rng1.Offset(0, 7).Copy
rng2.Offset(0, 1).PasteSpecial xlPasteValues
rng1.Offset(0, 12).Copy
rng2.Offset(0, 2).PasteSpecial xlPasteValues
rng2.Offset(0, -1).Resize(rng1.Cells.Count, 1) = "Monument"
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

The problem I have is that the values copied in column C starting at cell C4 carry across their formatting of all the text being in uppercase an example being BATTLE OF HASTINGS.

Could someone perhaps please show me how I would change the code so only the first letter of each word is in uppercase and the rest is in lowercase.

Kind regards

Chris

 

[c23lemon]

Hi,

I think there is no straightforward way of formatting those strings ie first letter upper case and the remaining characters as lowercase. I have written a small function to do this conversion.

Hope this helps!

Function modWords(WordsToSplit As String) As String

' Description: Converts all the first letters of a phrase / word etc to uppercase
'
' Declarations:
Dim iArr() As String, iCnt As Integer

iArr = Split(WordsToSplit, " ")

For iCnt = 0 To UBound(iArr)
modWords = modWords & UCase(Left(iArr(iCnt), 1)) & LCase(Mid(iArr(iCnt), 2, Len(iArr(iCnt)))) & " "
Next

modWords = Trim(modWords)

End Function

 

[Marcol]

Try using WorksheetFunction.Proper(), build it into your code.
Call like so in this example code

ProperCase Range("A1:A23")

Option Explicit
 
Sub ProperCase(rng As Range)
   Dim Cell As Range
 
   For Each Cell In rng
      Cell = WorksheetFunction.Proper(Cell)
   Next
End Sub
 
Sub ApplyProper()
   ProperCase Range("A1:A23")
End Sub

 

[c23lemon]

Thanks for sharing that function! Good to learn new things. Regards

 

[ir121973]

Hi, firstly many thanks for replying to my post, secondly apologies for not getting back to you sooner.

Thanks for the code, it really helped.

I did some more research on this and I found a piece of vba here: http://www.ozgrid.com/VBA/change-case-text.htm

I hope this may be of help to you or indeed to anyone else.

kind regards