Macro and a range of cells
 

Hi All,

I have a macro which replaces zeros with "-".
I would like to use the macro in any range
of cells but I don't know how to do it.
Despite I set any range of cells, the macro
always changes zeros into "-" in entire sheet.

I'd be very grateful for your help.

Regards
ME

Macro and a range of cells
 

Sub dash_it()
For Each r In Selection
If Not IsEmpty(r) And r.Value = 0 Then
r.Value = "-"
End If
Next
End Sub


select the cells and run the macro
--
Gary''s Student - gsnu200720


"ME" wrote:

Hi All,

I have a macro which replaces zeros with "-".
I would like to use the macro in any range
of cells but I don't know how to do it.
Despite I set any range of cells, the macro
always changes zeros into "-" in entire sheet.

I'd be very grateful for your help.

Regards
ME

Macro and a range of cells
 

Sorry - I misinterpretted the question - blanks are being seen as 0's. Good
thing Gary's Student was watching.
Will

"Gary''s Student" wrote:

Sub dash_it()
For Each r In Selection
If Not IsEmpty(r) And r.Value = 0 Then
r.Value = "-"
End If
Next
End Sub


select the cells and run the macro
--
Gary''s Student - gsnu200720


"ME" wrote:

Hi All,

I have a macro which replaces zeros with "-".
I would like to use the macro in any range
of cells but I don't know how to do it.
Despite I set any range of cells, the macro
always changes zeros into "-" in entire sheet.

I'd be very grateful for your help.

Regards
ME

Macro and a range of cells
 

ME
If you selected the range with the cursor before running the macro you could
use something like this:

dim R as Range
for each R in selection
if R.Value=0 then R.value="-"
next R

or if you have a named range (call it "NRange")

dim R as Range
for each R in Range("NRange")
if R.Value=0 then R.Value="-"
next r

Will

"ME" wrote:

Hi All,

I have a macro which replaces zeros with "-".
I would like to use the macro in any range
of cells but I don't know how to do it.
Despite I set any range of cells, the macro
always changes zeros into "-" in entire sheet.

I'd be very grateful for your help.

Regards
ME

Macro and a range of cells
 

Thank you very much. It works.:-)

Regards
ME

Sub dash_it()
For Each r In Selection
If Not IsEmpty(r) And r.Value = 0 Then
r.Value = "-"
End If
Next
End Sub


select the cells and run the macro
--
Gary''s Student - gsnu200720


"ME" wrote:

Hi All,

I have a macro which replaces zeros with "-".
I would like to use the macro in any range
of cells but I don't know how to do it.
Despite I set any range of cells, the macro
always changes zeros into "-" in entire sheet.

I'd be very grateful for your help.

Regards
ME

Macro and a range of cells
 

formatting accounting?

--
Don Guillett
SalesAid Software

"ME" wrote in message
...
Hi All,

I have a macro which replaces zeros with "-".
I would like to use the macro in any range
of cells but I don't know how to do it.
Despite I set any range of cells, the macro
always changes zeros into "-" in entire sheet.

I'd be very grateful for your help.

Regards
ME

Macro and a range of cells
 

Will
Your macro works too. Thanks a lot.

Regards
ME

ME
If you selected the range with the cursor before running the macro you could
use something like this:

dim R as Range
for each R in selection
if R.Value=0 then R.value="-"
next R

or if you have a named range (call it "NRange")

dim R as Range
for each R in Range("NRange")
if R.Value=0 then R.Value="-"
next r

Will

"ME" wrote:

Hi All,

I have a macro which replaces zeros with "-".
I would like to use the macro in any range
of cells but I don't know how to do it.
Despite I set any range of cells, the macro
always changes zeros into "-" in entire sheet.

I'd be very grateful for your help.

Regards
ME