Overflow Problem
 

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

Overflow Problem
 

I would assume that this is because the worksheet
UsedRange is too large and the loop attempts to execute on
each cell in the UsedRange. Debra Dalgleish describes this
issue and how to fix it at:

http://www.contextures.com/xlfaqApp.html#Unused

Note that the worksheet UsedRange property is not IMO the
best option for this. There are other ways to establish
the range(s) on which to run the macro, both fixed and
dynamic. You would need to provide a definition of the
ranges first.

Regards,
Greg

-----Original Message-----
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


.

Overflow Problem
 

This might help :-


Code
-------------------

If Application.WorksheetFunction.IsText(c.Value) Then
c.Value = Application.WorksheetFunction.Proper(c.Value)
End If

-------------------


--
Message posted from http://www.ExcelForum.com

Overflow Problem
 

See if this is better:

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

--
Jim Rech
Excel MVP
"D" wrote in message news:pJiSc.26663$Oi.7755@fed1read04...
| 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
|
|

Overflow Problem
 

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

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

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

"Jim Rech" wrote in message
...
See if this is better:

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

--
Jim Rech
Excel MVP
"D" wrote in message news:pJiSc.26663$Oi.7755@fed1read04...
| 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
|
|

Overflow Problem
 

Nope- adding this code STILL throws an overflow problem on the c.Value part
of line:
If Application.WorksheetFunction.IsText(c.Value) Then

"BrianB " wrote in message
...
This might help :-


Code:
--------------------

If Application.WorksheetFunction.IsText(c.Value) Then
c.Value = Application.WorksheetFunction.Proper(c.Value)
End If

--------------------



---
Message posted from http://www.ExcelForum.com/

Overflow Problem
 

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


"D" wrote in message
news:DZrSc.26686$Oi.14208@fed1read04...
Jim- Thank you for the response and code... I'm getting a
syntax error on
the

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

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

"Jim Rech" wrote in message
...
See if this is better:

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

--
Jim Rech
Excel MVP
"D" wrote in message
news:pJiSc.26663$Oi.7755@fed1read04...
| 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
|
|

Overflow Problem
 

SAWEET!!! IT WORKS!!! THANK YOU! THANK YOU!!!

D

"Chip Pearson" wrote in message
...
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


"D" wrote in message
news:DZrSc.26686$Oi.14208@fed1read04...
Jim- Thank you for the response and code... I'm getting a
syntax error on
the

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

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

"Jim Rech" wrote in message
...
See if this is better:

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

--
Jim Rech
Excel MVP
"D" wrote in message
news:pJiSc.26663$Oi.7755@fed1read04...
| 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
|
|