Convert all text to upper case with VBA

[DaveUK]

I have a workbook containing 1 worksheet "Recovered_Sheet1".

Please could someone tell me how i convert all cells in column "D" to upper case.
The is no fixed number of rows so i need code that starts at row 3 and converts all non empty cells in column D to upper case.
I need VBA not a worksheet function as i want to prevent users from "breaking" the worksheet formats.

Many thanks for any help.

 

[Smitty]

Take a look at Ivan Moala's Text Converter:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> TextCon()
<SPAN style="color:#007F00">'code by Ivan F. Moala</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> oCell <SPAN style="color:#00007F">As</SPAN> Range, Ans <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    Ans = Application.InputBox("Type in Letter" & vbCr & _
        "(L)owercase, (U)ppercase, (S)entence, (T)itles ")
    <SPAN style="color:#00007F">If</SPAN> Ans = "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> oCell <SPAN style="color:#00007F">In</SPAN> Selection     <SPAN style="color:#007F00">'.SpecialCells(xlCellTypeConstants, 2)</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> UCase(Ans)
            Case "L": oCell = LCase(oCell.Text)
            Case "U": oCell = UCase(oCell.Text)
            <SPAN style="color:#00007F">Case</SPAN> "S": oCell = UCase(Left(oCell.Text, 1)) & _
                LCase(Right(oCell.Text, Len(oCell.Text) - 1))
            Case "T": oCell = Application.WorksheetFunction.Proper(oCell.Text)
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[DaveUK]

I tried this code on Excel 2000 (9.0.6926 SP-3) version and i get this error on the Ucase part of the code.

My code is:

Option Explicit

Sub TextConvertToUpperCase()
    
    With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    End With
    
    Dim ConvertCellToUpperCase As Range
      
    With Sheets("Recovered_Sheet1")
     .Range("D9", Range("D9").End(xlDown)).Select
    
        For Each ConvertCellToUpperCase In Selection
    
            ConvertCellToUpperCase = UCase(ConvertCellToUpperCase.Text)
            
        Next
    
    End With
    
    Range("A1").Select
    
    With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    End With
 
End Sub

The error i get is:

Compile Error: Can't find project or library


Please can someone suggest a work around or suggest the cure for the error.

TIA

 

[Norie]

That sounds to me that you may have some missing references.

In the VBA editor goto Tools>References... and see if any entries have MISSING nect to them. If they do uncheck them.

If that's not the problem, I'm afraid the workbook might have become corrupt.

 

[Smitty]

can someone suggest a work around

Ivan's code. I modified it so it will only work on a selected range.

But this works:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> TextConvertToUpperCase()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    
    <SPAN style="color:#00007F">With</SPAN> Application
        .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
        .Calculation = xlCalculationManual
        
        <SPAN style="color:#00007F">With</SPAN> Sheets("Recovered_Sheet1")
            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> .Range([D9], [D9].End(xlDown))
                c = UCase(c.Text)
            <SPAN style="color:#00007F">Next</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        
        .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
        .Calculation = xlCalculationAutomatic
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
  
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Smitty