PDA

View Full Version : Populate empty cells with zeroes?



spflash
06-09-2006, 05:27 AM
Hi All,

Using the code below I'm attempting to populate all empty cells in a given range with zeroes. I'm able to get the loop that takes care of the rows (i.e.- i's) to work by itself. However, when I nest this loop inside the one for columns (i.e. - j's) I get errors. Can anyone help me create code that populates the rows and columns that are empty with zeroes?
Thanks

spflashSub Zeroes
Dim FinalRow As Long
Dim FinalCol As Long
FinalRow = Cells(65536, 1).End(xlUp).Row
FinalCol = Cells(1, 255).End(xlToLeft).Column
For i = 2 To FinalRow
For j = 8 To FinalCol
If Cells(i, j).Value = "" Then
Cells(i, j) = "0"
End If
Next j
Next i
End Sub

mvidas
06-09-2006, 05:36 AM
Hi spflash, On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = 0
On Error GoTo 0You need the On Error Resume Next in case the SpecialCells method doesnt return anything, but this to me seems much easier.
As to why yours isn't working, the only thing that I could think of is if the FinalCol is less than 8, it wouldn't process through. Otherwise it looks fine.
Matt

mdmackillop
06-09-2006, 07:14 AM
Hi spflash,
Your code as written will insert 0 as a string, not a value as in Matt's code.
Remember to Dim your i and j variables as well.
Regards
MD

lawrenb
06-09-2006, 07:14 AM
I lloked at your code.

I got the code to work by adding .Value to your cell reference after THEN

For j = 8 To FinalCol If Cells(i, j).Value = "" Then Cells(i, j).VALUE ="0"