All,
I have a macro that copies two columns to a new workbook and a third row
adds up each cell in columns 1 and two. This script is part of a much
larger macro. The problem is that every now and then one of the cells that
is being copied over has a hidden space or character that causes the value
in column three to list #Value! instead of the correct amount. How can I
have the macro remove any hidden spaces/characters, etc so that when the
third column is adding up the two columns, I do not get the #Value! output?

Thanks

John

There could be a few changes that you make.

I would think that the easiest would be to just use =sum() in your formula.
=Sum() ignores text.

If there's a hidden space or character that affects the sum (maybe you have 123_
(where _ represents a character that makes that cell text)), but you still want
to include 123 in the sum, then cleaning the data would be the best way to go.

You may want to try David McRitchie's routine to clean the data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

John wrote:

All,
I have a macro that copies two columns to a new workbook and a third row
adds up each cell in columns 1 and two. This script is part of a much
larger macro. The problem is that every now and then one of the cells that
is being copied over has a hidden space or character that causes the value
in column three to list #Value! instead of the correct amount. How can I
have the macro remove any hidden spaces/characters, etc so that when the
third column is adding up the two columns, I do not get the #Value! output?

Thanks

John

--

Dave Peterson

Thanks Dave,
Using the sum function did the trick. It is amazing how sometimes the simple
things are most effective.

John



"Dave Peterson" wrote in message
...
There could be a few changes that you make.

I would think that the easiest would be to just use =sum() in your
formula.
=Sum() ignores text.

If there's a hidden space or character that affects the sum (maybe you
have 123_
(where _ represents a character that makes that cell text)), but you still
want
to include 123 in the sum, then cleaning the data would be the best way to
go.

You may want to try David McRitchie's routine to clean the data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

John wrote:

All,
I have a macro that copies two columns to a new workbook and a third row
adds up each cell in columns 1 and two. This script is part of a much
larger macro. The problem is that every now and then one of the cells
that
is being copied over has a hidden space or character that causes the
value
in column three to list #Value! instead of the correct amount. How can I
have the macro remove any hidden spaces/characters, etc so that when the
third column is adding up the two columns, I do not get the #Value!
output?

Thanks

John

--

Dave Peterson