Macro Find & Replace problem

Giganews Newsgroups
Subject: Macro Find & Replace problem
Posted by:  Craig Helm (sancho@remove#this.melbpc.org.this-also.au)
Date: Wed, 22 Oct 2003

I send off a spreadsheet to a central payroll office once a week that
has links to many other files.

One column has the occasional entry, the rest blank. Payroll demands
that it have nothing but the relevant data (ie there is no links in
the column when they receive it.)

I recorded a macro that copies then pastes the values just prior to
sending. That got rid of the links and kept the appropriate values.
Unfortunately it leaves a 0 in the cells that is only visible in the
formula bar. This also causes problems for them.

I thought I had a brainwave - Find & Replace 0 with "". Worked a
treat, until I realised that it also stripped 0's from 10's, making
them a 1 !

My next plan would be to write something that said:
IF less than 1 then delete, otherwise leave value in cell.

Although familiar with recording macro's, and picking out bits from
other's macro's I have little experience at writing macro's from
scratch. I'd appreciate assistance, even if there is a better way of
eliminating the zero's without a macro I'd be interested in seeing how
such an IF statement should be written.

And no, it does not seem related to the Tools-Options setting of Zero
Values.

Thanks,
Craig

Replies