PDA

View Full Version : Solved: how to convert first later to upper case and after onward lower case?



rrosa1
11-19-2010, 09:58 PM
i try this code in my sh
but it go in loop and do nothing
can some one help me

Option Explicit

Sub ChangeCase_5()
Dim r As Range, RNG As Range, s As Double, c As Double, d As Double
Dim LR As Long
Application.ScreenUpdating = False

With Worksheets("Report")

LR = .Range("C" & .Rows.Count).End(xlUp).Row
Set r = .Range("C5:C" & LR)

Do
Set RNG = Nothing

On Error Resume Next

Set RNG = .Range("c6:C" & LR).SpecialCells(xlCellTypeVisible) 'if no items found
RNG.Value = StrConv(RNG, vbProperCase)
RNG.Offset(, 10).Value = "RNG"
Loop
End With

ExitHandler:
Set r = Nothing
Application.ScreenUpdating = True
End Sub

Aussiebear
11-20-2010, 02:39 AM
Have a look at Proper case

I would assume it should be something like this

For each RNG.Value in r
RNG.Value = StrConv(RNG, vbProperCase)
Next RNG.Value

or something similar. I could well be wrong but my assumption is just using the line RNG.Value= StrConv(RNG, vbProperCase) is like saying "kick the ball" rather than saying something like
For each ball that comes near you during the game,
kick the ball
Next ball

p45cal
11-20-2010, 03:12 AM
to get you started:Sub ChangeCase_5()
Dim cll As Range, RNG As Range, s As Double, c As Double, d As Double
Dim LR As Long
Application.ScreenUpdating = False

With Worksheets("Report")
LR = .Range("C" & .Rows.Count).End(xlUp).Row
Set RNG = Nothing
On Error Resume Next
Set RNG = .Range("c6:C" & LR).SpecialCells(xlCellTypeVisible) 'if no items found
For Each cll In RNG.Cells
cll.Value = StrConv(cll.Value, vbProperCase)
cll.Offset(, 10).Value = cll.Value
Next cll
End With

ExitHandler:
Application.ScreenUpdating = True
End Sub

rrosa1
11-20-2010, 04:45 AM
hi
Aussiebear and
p45cal
it's work fine
thanks