Overflow Problem

D

D

Hey guys-
Got this code from the net, seems to work fine until now. I'm getting an
Error 6 Overflow problem. I don't understand VB much at all, so can someone
point me along the way?
Thanks
D

Sub Step4()
' ProperCase Macro
Dim c As Range
With ActiveSheet
For Each c In .UsedRange
c.Value = Application.WorksheetFunction.Proper(c.Value)
Next
End With

End Sub
 
J

Jim Rech

See if this is better:

Sub ConvertUsedRgToProperCase()
Dim Cell As Range
On Error GoTo EndThis
For Each Cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants,
xlTextValues)
Cell.Value = StrConv(Cell.Value, vbProperCase)
Next
EndThis:
End Sub

--
Jim Rech
Excel MVP
| Hey guys-
| Got this code from the net, seems to work fine until now. I'm getting an
| Error 6 Overflow problem. I don't understand VB much at all, so can
someone
| point me along the way?
| Thanks
| D
|
| Sub Step4()
| ' ProperCase Macro
| Dim c As Range
| With ActiveSheet
| For Each c In .UsedRange
| c.Value = Application.WorksheetFunction.Proper(c.Value)
| Next
| End With
|
| End Sub
|
|
 
D

D

Jim- Thank you for the response and code... I'm getting a syntax error on
the

For Each Cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants,
xlTextValues)

Any ideas? I don't see it...
Thanks!
D
 
C

Chip Pearson

The code should be on a single line, or split using the line
continuation character _. E.g.,

For Each Cell In ActiveSheet.UsedRange.SpecialCells( _
xlCellTypeConstants, xlTextValues)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Top