Hi everyone,
I have a range of 30 cells in a column, but most have numbers, and some are
blank. I need to write a procedure to replace blank cells with zeros, and to
skip over existing numbers. I need to keep this within the 30 cells in my
range. Any help would be greatly appreciated!
Thank you,
Electricbluelady

Select the top cell and run:

Sub zero_filler()
For i = 0 To 29
If IsEmpty(Selection.Offset(i, 0).Value) Then
Selection.Offset(i, 0).Value = 0
End If
Next
End Sub

--
Gary''s Student - gsnu200793


"electricbluelady" wrote:

Hi everyone,
I have a range of 30 cells in a column, but most have numbers, and some are
blank. I need to write a procedure to replace blank cells with zeros, and to
skip over existing numbers. I need to keep this within the 30 cells in my
range. Any help would be greatly appreciated!
Thank you,
Electricbluelady

Hi "Gary"s Student":
Thank you so much! It ran like a dream. You are awesome!
Regards,
--
Thank you,
Electricbluelady


"Gary''s Student" wrote:

Select the top cell and run:

Sub zero_filler()
For i = 0 To 29
If IsEmpty(Selection.Offset(i, 0).Value) Then
Selection.Offset(i, 0).Value = 0
End If
Next
End Sub

--
Gary''s Student - gsnu200793


"electricbluelady" wrote:

Hi everyone,
I have a range of 30 cells in a column, but most have numbers, and some are
blank. I need to write a procedure to replace blank cells with zeros, and to
skip over existing numbers. I need to keep this within the 30 cells in my
range. Any help would be greatly appreciated!
Thank you,
Electricbluelady

On 20 Jun., 21:23, electricbluelady
wrote:
Hi everyone,
I have a range of 30 cells in a column, but most have numbers, and some are
blank. I need to write a procedure to replace blank cells with zeros, and to
skip over existing numbers. I need to keep this within the 30 cells in my
range. Any help would be greatly appreciated!
Thank you,
Electricbluelady

Hi

Try this macro, just change the TargetRange to suit your needs.

Dim TargetRange As Range

Sub FillWithZero()
Set TargetRange = Range("A1:A30")

For Each cell In TargetRange
If cell.Value = "" Then
cell.Value = 0
End If
Next
End Sub

Regards,
Per

Hi Jessen,
Thank you so much! You came to my rescue. I truly appreciate it. :)
Regards,
--
Thank you,
Electricbluelady


"Per Jessen" wrote:

On 20 Jun., 21:23, electricbluelady
wrote:
Hi everyone,
I have a range of 30 cells in a column, but most have numbers, and some are
blank. I need to write a procedure to replace blank cells with zeros, and to
skip over existing numbers. I need to keep this within the 30 cells in my
range. Any help would be greatly appreciated!
Thank you,
Electricbluelady

Hi

Try this macro, just change the TargetRange to suit your needs.

Dim TargetRange As Range

Sub FillWithZero()
Set TargetRange = Range("A1:A30")

For Each cell In TargetRange
If cell.Value = "" Then
cell.Value = 0
End If
Next
End Sub

Regards,
Per

try this:
(untested)

Sub electricbluelady()

dim myRange as range
dim r as range

set myRange = activesheet.range("a1:d50") '<=== change to suit range

for each r in myRange
if r.value = "" then
r.value = 0
end if
next r

End Sub

this will loop through each cell in the range, and if the range is
blank, it will insert a zero.
hope it helps!
:)
susan


On Jun 20, 3:23*pm, electricbluelady
wrote:
Hi everyone,
I have a range of 30 cells in a column, but most have numbers, and some are
blank. I need to write a procedure to replace blank cells with zeros, and to
skip over existing numbers. I need to keep this within the 30 cells in my
range. Any help would be greatly appreciated!
Thank you,
Electricbluelady

Hi Susan,
Thank you! It has helped a lot. I can see other places where I can run this
code. You made it much simpler. I sure appreciate it.
Regards,
--
Thank you,
Electricbluelady


"Susan" wrote:

try this:
(untested)

Sub electricbluelady()

dim myRange as range
dim r as range

set myRange = activesheet.range("a1:d50") '<=== change to suit range

for each r in myRange
if r.value = "" then
r.value = 0
end if
next r

End Sub

this will loop through each cell in the range, and if the range is
blank, it will insert a zero.
hope it helps!
:)
susan


On Jun 20, 3:23 pm, electricbluelady
wrote:
Hi everyone,
I have a range of 30 cells in a column, but most have numbers, and some are
blank. I need to write a procedure to replace blank cells with zeros, and to
skip over existing numbers. I need to keep this within the 30 cells in my
range. Any help would be greatly appreciated!
Thank you,
Electricbluelady

Give this code a try (just define the range in the Set statement; everything
after the Set statement works automatically)...

Dim MyRange As Range
Set MyRange = Worksheets("Sheet3").Range("H8:H36")
If MyRange(MyRange.Count).Value = "" Then MyRange(MyRange.Count).Value = 0
MyRange.SpecialCells(xlCellTypeBlanks).Value = "0"

Rick


"electricbluelady" wrote in
message ...
Hi everyone,
I have a range of 30 cells in a column, but most have numbers, and some
are
blank. I need to write a procedure to replace blank cells with zeros, and
to
skip over existing numbers. I need to keep this within the 30 cells in my
range. Any help would be greatly appreciated!
Thank you,
Electricbluelady

Hi Rick,
Thank you for your response. It worked perfectly. I appreciate your help!
Regards,,
--
Thank you,
Electricbluelady


"Rick Rothstein (MVP - VB)" wrote:

Give this code a try (just define the range in the Set statement; everything
after the Set statement works automatically)...

Dim MyRange As Range
Set MyRange = Worksheets("Sheet3").Range("H8:H36")
If MyRange(MyRange.Count).Value = "" Then MyRange(MyRange.Count).Value = 0
MyRange.SpecialCells(xlCellTypeBlanks).Value = "0"

Rick


"electricbluelady" wrote in
message ...
Hi everyone,
I have a range of 30 cells in a column, but most have numbers, and some
are
blank. I need to write a procedure to replace blank cells with zeros, and
to
skip over existing numbers. I need to keep this within the 30 cells in my
range. Any help would be greatly appreciated!
Thank you,
Electricbluelady

Manually, you could select that range and then do
Edit|Replace
what: (leave blank)
with: 0
replace all

You could also:
Select the range
Edit|goto|Special|Blanks
type 0
but hit ctrl-enter to fill the empty cells.

electricbluelady wrote:

Hi everyone,
I have a range of 30 cells in a column, but most have numbers, and some are
blank. I need to write a procedure to replace blank cells with zeros, and to
skip over existing numbers. I need to keep this within the 30 cells in my
range. Any help would be greatly appreciated!
Thank you,
Electricbluelady

--

Dave Peterson