Remove blanks

[freddie mac]

I have posted questions concerning this problem on other forums. I have
macro that does some calculations. It works like this.

1) The user is to paste info (from another program viz. SimCorp
Dimension) onto a spreadsheet and then press a button.
2) when pressing the button my macro is started. The first part of the
macro is to search the info on the spreadsheet for any uneccessary
blanks. Sometime the values that the user pastes into the spreadsheet
are written with thousand separators e.g. "4 000 000". Since I want to
use the values for computations I must remove these blanks.
3) Calculations are made.

The problem is that I do find and remove the blanks but in doing so the
format or some other type of setting is changed for the cells that have
had blanks (that are now removed). The cells looks fine and you can add
the in the spreadsheet one by one. However if you try to use the sum
function or I try to add them together in the macro it does not work.
The code for finding and removing blanks ( I got this from Norman Jones
who has helped me alot) is:

Public Sub findAndRemoveBlanks()
ActiveSheet.UsedRange.Replace What:=Chr(160), _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub

If you have any idea on how to solve this I would be most greatful for
your help. Also if anyone should be willing to look at my spreadsheet
(with the values inside it) I think that would be very good since I
really have tried to solve and I have gotten alot of help on various
discussion forums but I still cant solve it. Thank you all very much!!!

 

[Scoops]

macro is to search the info on the spreadsheet for any uneccessary
blanks. Sometime the values that the user pastes into the spreadsheet
are written with thousand separators e.g. "4 000 000". Since I want to
use the values for computations I must remove these blanks.

The problem is that I do find and remove the blanks but in doing so the
format or some other type of setting is changed for the cells that have
had blanks (that are now removed). The cells looks fine and you can add
the in the spreadsheet one by one. However if you try to use the sum
function or I try to add them together in the macro it does not work.
The code for finding and removing blanks ( I got this from Norman Jones
who has helped me alot) is:

Public Sub findAndRemoveBlanks()
ActiveSheet.UsedRange.Replace What:=Chr(160), _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub

If you have any idea on how to solve this I would be most greatful for
your help. Also if anyone should be willing to look at my spreadsheet
(with the values inside it) I think that would be very good since I
really have tried to solve and I have gotten alot of help on various
discussion forums but I still cant solve it. Thank you all very much!!!

Hi freddie

I've just typed some text in the format you've outlined into a sheet,
applied the Replace (space) with (nothing) and the figures SUMmed with
no problem.

Having done the Replace, try:

Type 1 into a cell and copy it

Select the range of "numbers" and Edit > PasteSpecial > Multiply

If that works you could apply it in your macro

Regards

Steve