Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow
I am modifying a macro built long ago by others, and am close to completion.
The macro defines a range of rows, then goes to column F, and for every cell that is empty, it puts a space. The problem now is an overflow error on the "For x = 8 To NumberVal" line below. Previously, I was getting this error on the "NumberVal = Right(LastCell, (Len(LastCell) - 1))" line, but changing the NumberVal to Long, from Integer, solved that. I am clueless on the Overflow error topic, so any help would be greatly appreciated. Sub Stop_Wrapping() Dim LastCell Dim C_LastCell ' Changed the next from Integer to Long Dim NumberVal As Long Dim temp Dim x As Integer Dim y As Integer Dim Delete_Flag As Boolean Dim RightNow As Date x = 2 RightNow = Date 'Find the last populated cell in the 'A' column and setup for all other columns Range("A1").Select LastCell = ActiveCell.SpecialCells(xlLastCell).Address(RowAbs olute:=False, _ ColumnAbsolute:=False) NumberVal = Right(LastCell, (Len(LastCell) - 1)) C_LastCell = "C" & NumberVal y = 8 For x = 8 To NumberVal Delete_Flag = False Range("F" & x).Select If ActiveCell.Value = "" Then ActiveCell.Value = " " End If Next x End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow
Give this a try for adding in the blanks...
Sub AddBlank() Dim rng As Range On Error Resume Next Set rng = Columns("F").SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Not rng Is Nothing Then rng.Value = " " End Sub -- HTH... Jim Thomlinson "jmdaniel" wrote: I am modifying a macro built long ago by others, and am close to completion. The macro defines a range of rows, then goes to column F, and for every cell that is empty, it puts a space. The problem now is an overflow error on the "For x = 8 To NumberVal" line below. Previously, I was getting this error on the "NumberVal = Right(LastCell, (Len(LastCell) - 1))" line, but changing the NumberVal to Long, from Integer, solved that. I am clueless on the Overflow error topic, so any help would be greatly appreciated. Sub Stop_Wrapping() Dim LastCell Dim C_LastCell ' Changed the next from Integer to Long Dim NumberVal As Long Dim temp Dim x As Integer Dim y As Integer Dim Delete_Flag As Boolean Dim RightNow As Date x = 2 RightNow = Date 'Find the last populated cell in the 'A' column and setup for all other columns Range("A1").Select LastCell = ActiveCell.SpecialCells(xlLastCell).Address(RowAbs olute:=False, _ ColumnAbsolute:=False) NumberVal = Right(LastCell, (Len(LastCell) - 1)) C_LastCell = "C" & NumberVal y = 8 For x = 8 To NumberVal Delete_Flag = False Range("F" & x).Select If ActiveCell.Value = "" Then ActiveCell.Value = " " End If Next x End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow
Have you tried using Range.Replace?
Range("F8:F" & NumberVal).Replace What:="", Replacement:=" ", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Charles jmdaniel wrote: I am modifying a macro built long ago by others, and am close to completion. The macro defines a range of rows, then goes to column F, and for every cell that is empty, it puts a space. The problem now is an overflow error on the "For x = 8 To NumberVal" line below. Previously, I was getting this error on the "NumberVal = Right(LastCell, (Len(LastCell) - 1))" line, but changing the NumberVal to Long, from Integer, solved that. I am clueless on the Overflow error topic, so any help would be greatly appreciated. Sub Stop_Wrapping() Dim LastCell Dim C_LastCell ' Changed the next from Integer to Long Dim NumberVal As Long Dim temp Dim x As Integer Dim y As Integer Dim Delete_Flag As Boolean Dim RightNow As Date x = 2 RightNow = Date 'Find the last populated cell in the 'A' column and setup for all other columns Range("A1").Select LastCell = ActiveCell.SpecialCells(xlLastCell).Address(RowAbs olute:=False, _ ColumnAbsolute:=False) NumberVal = Right(LastCell, (Len(LastCell) - 1)) C_LastCell = "C" & NumberVal y = 8 For x = 8 To NumberVal Delete_Flag = False Range("F" & x).Select If ActiveCell.Value = "" Then ActiveCell.Value = " " End If Next x End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow
An integer will max out at 2^15 -1 (32,767), where a long will go to 2^31 -1
(2,147,483,647). Since your code deals with rows in a sheet (you can have more than 32k rows), an integer is not sufficient to handle your needs. This is an overflow situation: trying to assign a value to a variable that is not of appropriate size. Make the following change: Old: Dim x As Integer New: Dim x As long "jmdaniel" wrote: I am modifying a macro built long ago by others, and am close to completion. The macro defines a range of rows, then goes to column F, and for every cell that is empty, it puts a space. The problem now is an overflow error on the "For x = 8 To NumberVal" line below. Previously, I was getting this error on the "NumberVal = Right(LastCell, (Len(LastCell) - 1))" line, but changing the NumberVal to Long, from Integer, solved that. I am clueless on the Overflow error topic, so any help would be greatly appreciated. Sub Stop_Wrapping() Dim LastCell Dim C_LastCell ' Changed the next from Integer to Long Dim NumberVal As Long Dim temp Dim x As Integer Dim y As Integer Dim Delete_Flag As Boolean Dim RightNow As Date x = 2 RightNow = Date 'Find the last populated cell in the 'A' column and setup for all other columns Range("A1").Select LastCell = ActiveCell.SpecialCells(xlLastCell).Address(RowAbs olute:=False, _ ColumnAbsolute:=False) NumberVal = Right(LastCell, (Len(LastCell) - 1)) C_LastCell = "C" & NumberVal y = 8 For x = 8 To NumberVal Delete_Flag = False Range("F" & x).Select If ActiveCell.Value = "" Then ActiveCell.Value = " " End If Next x End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow
Perfect. Thanks for the help, as well as those from the other 2 folks.
"Bill Pfister" wrote: An integer will max out at 2^15 -1 (32,767), where a long will go to 2^31 -1 (2,147,483,647). Since your code deals with rows in a sheet (you can have more than 32k rows), an integer is not sufficient to handle your needs. This is an overflow situation: trying to assign a value to a variable that is not of appropriate size. Make the following change: Old: Dim x As Integer New: Dim x As long "jmdaniel" wrote: I am modifying a macro built long ago by others, and am close to completion. The macro defines a range of rows, then goes to column F, and for every cell that is empty, it puts a space. The problem now is an overflow error on the "For x = 8 To NumberVal" line below. Previously, I was getting this error on the "NumberVal = Right(LastCell, (Len(LastCell) - 1))" line, but changing the NumberVal to Long, from Integer, solved that. I am clueless on the Overflow error topic, so any help would be greatly appreciated. Sub Stop_Wrapping() Dim LastCell Dim C_LastCell ' Changed the next from Integer to Long Dim NumberVal As Long Dim temp Dim x As Integer Dim y As Integer Dim Delete_Flag As Boolean Dim RightNow As Date x = 2 RightNow = Date 'Find the last populated cell in the 'A' column and setup for all other columns Range("A1").Select LastCell = ActiveCell.SpecialCells(xlLastCell).Address(RowAbs olute:=False, _ ColumnAbsolute:=False) NumberVal = Right(LastCell, (Len(LastCell) - 1)) C_LastCell = "C" & NumberVal y = 8 For x = 8 To NumberVal Delete_Flag = False Range("F" & x).Select If ActiveCell.Value = "" Then ActiveCell.Value = " " End If Next x End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Overflow | Excel Programming | |||
Overflow error | Excel Programming | |||
Overflow Problem | Excel Programming | |||
VBA overflow | Excel Programming | |||
VBA overflow | Excel Programming |