Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 | | |
#5
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 | | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 | | |
#8
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Overflow when translating from C++ HELP!! | Excel Programming | |||
overflow error | Excel Programming | |||
Overflow in VBA, but value < limit | Excel Programming | |||
VBA overflow | Excel Programming | |||
VBA overflow | Excel Programming |