VBA script to change contents of column b in uppercase

[rmehta]

Hi,

I have a work book with several worksheets within, I would like to turn the contents of Column B within Sheet1-Sheet4 into Uppercase by using a VBA script

Any help would be much appreciated

 

[VoG]

Hi. Try this

Sub tocaps()
Dim ws As Worksheet, LR As Long, i As Long
For Each ws In Worksheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4"))
    With ws
        LR = .Range("B" & Rows.Count).End(xlUp).Row
        For i = 1 To LR
            .Range("B" & i).Value = UCase(.Range("B" & i).Value)
        Next i
    End With
Next ws
End Sub

 

[JackDanIce]

Try:

Sub UpperCase()
Dim i As Long, j As Long
For j = 1 To 4    
    With Sheets(j)
        For i = 1 To .Range("B" & Rows.Count).End(xlUp).Row
            .Range("B" & i) = UCase(.Range("B" & i))
        Next i
    End With
Next j
End Sub

 

[Dazzawm]

I have always found this code very useful. You select the column or cells you wish to change and an input box appears giving you the choice of Upper, Lower & Proper case.

Sub CaseSize()
'Changes case size in the selected range of cells
Dim myCase, rng As Range, r As Range
myCase = Application.InputBox("Enter" & vbLf & "1 for Upper Case" & vbLf & _
"2 for Lower Case" & vbLf & "3 for Proper Case", Type:=1)
If (myCase = False) + (Not myCase Like "[1-3]") Then Exit Sub
On Error Resume Next
Set rng = Selection.SpecialCells(2)
On Error GoTo 0
If Not rng Is Nothing Then
For Each r In rng
r.Value = StrConv(r.Value, myCase)
Next
End If
End Sub