Overflow

[Guest]

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(RowAbsolute:=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

 

[Guest]

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

 

[Die_Another_Day]

Have you tried using Range.Replace?
Range("F8:F" & NumberVal).Replace What:="", Replacement:=" ",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Charles

 

[Guest]

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

 

[Guest]

Perfect. Thanks for the help, as well as those from the other 2 folks.