Macro

E

EZK

Hi,

I was using the following macro for a very long time:

Sub Dash()

Dim komor As Range
For Each komor in ActiveSheet.UsedRange
If IsNumeric(komor.Value) = True Then
If komor.Value = 0 Then
komor.Value = "-"
Else
komor.Value = komor
End If
End If
Next komor

End Sub


The purpose of the macro is to replace zero values with "-".
I used it in big Excel files and then I used the Mail Merge
to get Word pages where the "-" means that the value
does not exist. In Word the values are in tables.
All It worked very well when I used Word 97 and Excel 97.

Now I have Word 2007 and Excel 2007. The macro works
properly, in the Excel sheet all zero values are replaced with "-".
However after the Mail Merge (prepared the same way as it
was in the 97) on the Word pages some of the values are
printed as zero, not as "-". (The others are printed properly
as "-").

The Excel files are really big, so the Word files include
a lot od pages and it is very difficult to change it manually.

I would be very grateful if someone could help me.

Regards,
EZK
 
D

Dave O

Hi, EZK,
Your note says the Word doc displays some entries as a dash and some
as zero. I think the key to your answer will be some characteristic of
the cells that display the dash properly: they may be formatted in a
particular way, for instance.

The only other think I can think of would be to change your code
slightly, perhaps to print two dashes in the cells with zero values.
If Word picks that up as two dashes, you could do a search-and-replace
using Word to replace two dashes with a single dash.

Good luck with it-
Dave O
Eschew obfuscation
 
D

Dave O

PS, forgot to mention: if it does wind up being a formatting
difference, you can revise your code to format all those cells
identically so they'll show up in Word as a dash.

Dave O
Eschew obfuscation
 
E

EZK

Hi Dave O,

Thanks for your reply.
Your note says the Word doc displays some entries as a dash and some
as zero. I think the key to your answer will be some characteristic of
the cells that display the dash properly: they may be formatted in a
particular way, for instance.
Click to expand...

I also thought that the key to answer could be in differences in formatting
of cells which were replaced properly and thouse which were not.
That can be the reason because the "wrong" cells are in the same columns.
But the problem is that I do not know how to check and/or change the format
of the cells. All of cells are shown as general type.

Regards,
EZK
 
E

EZK

Hi Dave O again,
The only other think I can think of would be to change your code
slightly, perhaps to print two dashes in the cells with zero values.
If Word picks that up as two dashes, you could do a search-and-replace
using Word to replace two dashes with a single dash.
Click to expand...

I have just tried it with two dashes and it works!
It makes the way a little bit longer but it works. This is the most important
thing at the moment.

Thank you very much.

Regards,
EZK
 
E

EZK

I have just tried it with two dashes and it works!
It makes the way a little bit longer but it works. This is the most important
thing at the moment.
Click to expand...

Ynfortunately it dows not work in real example.:-( I do not know why.
I do not know what the difference is between the small and big example.

I think I'll have to find a difference in formatting but at the moment I do not
know how.

Regards
EZK
 
D

Dave O

Hi, EZK:

Here's how to modify your code to format all the "dash" cells to
general format- see the line added below:

Sub Dash()
Dim komor As Range
For Each komor in ActiveSheet.UsedRange
If IsNumeric(komor.Value) = True Then
If komor.Value = 0 Then
'NEW LINE ADDED HERE
komor.NumberFormat = "General"
komor.Value = "-"
Else
komor.Value = komor
End If
End If
Next komor
End Sub

Just to make sure I keep up with your communications, please contact
me directly at
CYCLEZEN atsign YAHOO dot COM (no spaces and convert the lower case
words to characters)

Dave O
Eschew obfuscation
 
D

Dave O

You might try setting those cells to text:
'NEW LINE ADDED HERE
komor.NumberFormat = "@"
 
Top