how to change list of empty cells with a value ?

[Excel2003] If is not so easy to input repeated value to a different cell location.
And so much moving cursor and pasting to do the job! Is there any solution ?

if this is the selection of cells, and "-" means empty, new value to be replace = "x
"
a
-
-
b
c
-

becomes

a
x
x
b
c
x

how to code it using the current region to replace current empty cell with "x" ?

thanks for helping hands! I really need this to speed up my work!

You'll have to modify this a bit to suit your workbook but try this:

Sub replaceBlanks()
    For Each loopCell In Worksheets("Sheet1").Range("A1:A6")
        If loopCell.Value = "" Then loopCell.Value = "x"
    Next loopCell
End Sub

Originally Posted by Mordred

You'll have to modify this a bit to suit your workbook but try this:

Sub replaceBlanks()
    For Each loopCell In Worksheets("Sheet1").Range("A1:A6")
        If loopCell.Value = "" Then loopCell.Value = "x"
    Next loopCell
End Sub

thanks bro Mordred,

Let the user decided which region to modify!
but why this one is not working ?

For Each c In ActiveCell.CurrentRegion.Cells
        If c.Value="" Then c.Value = "X"
Next

Please put all code in code tags. I'll do it for you now, but remember it next time, please.

For a VBA free approach, select the range of cells, hit F5, click Special, tick Blanks, hit OK, type "x", hold down Ctrl and hit enter.

cheers,

Mayhap teylyn's approach is better because my approach does not let the user decide which range to select.

As far as this goes:

For Each c In ActiveCell.CurrentRegion.Cells
        If c.Value="" Then c.Value = "X"
Next

It will replace blank cells in a region with x. For instance, if you only want to have a range down column A to run the code but range("C5") is selected then all cells from range A1 to C5 will be referenced and then all the empty cells will be replace with x (sorry for the run-on).

thanks Teyln and Mordred.

your solution is excellent... like knife cutting through the butter...

if someone prefer in code (TESTED and worked),

Sub text2blank()
' This macro :
' replace selection with xStr for any blank value
    xStr = Application.InputBox( _
    prompt:="What Text to be inserted to Blank : ", Type:=2)
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = xStr
End Sub

I love this forum....

Thanks for the feedback, Johan. Stick around. You'll love it even more.

Why not try and answer some questions?

cheers,