I have a problem with a macro. The macro searches a spreadsheet for thousand
separator blanks and other uneccessary blanks. The contents of the
spreadsheet are pasted onto the spreadsheet. Therefore when running the macro
the program stores all values as text, so I cannot use them in any
calculations. How do you solve this? I have tried recording a macro when
changing the format in excel but it does not work? Pleaase please help me , I
really dont understand it!!!!!Thank you very much!!!

This is the code that I use in a sub for removing the blanks:
Dim WB As Workbook
Dim SH As Worksheet
Dim rng, rCell As Range
Set WB = ActiveWorkbook
Set SH = WB.Sheets("Beräkning")
Set rng = SH.UsedRange
'Set rng = SH.Range("A1:p100")

For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not UCase(.Value) Like "*[A-Z]*" Then
.Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell

Any help is ver much appreciated!!
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False


I have also tried (but it does not work when pasting):

Hi Sally,

Could you explain:

The contents of the spreadsheet are pasted onto the spreadsheet.
Therefore when running the macro the program stores all values as text

The macro merely romoves spaces from non-alpha cells and, for example,
coverts a text entry of 200 00 to a numeric 20000.

Incidentally, you should change:

.Replace What:=" ", Replacement:=""

to

.Replace What:=" ", Replacement:="", LookAt:=xlPart


---
Regards,
Norman



"Sally Mae" wrote in message
...
I have a problem with a macro. The macro searches a spreadsheet for
thousand
separator blanks and other uneccessary blanks. The contents of the
spreadsheet are pasted onto the spreadsheet. Therefore when running the
macro
the program stores all values as text, so I cannot use them in any
calculations. How do you solve this? I have tried recording a macro when
changing the format in excel but it does not work? Pleaase please help me
, I
really dont understand it!!!!!Thank you very much!!!

This is the code that I use in a sub for removing the blanks:
Dim WB As Workbook
Dim SH As Worksheet
Dim rng, rCell As Range
Set WB = ActiveWorkbook
Set SH = WB.Sheets("Beräkning")
Set rng = SH.UsedRange
'Set rng = SH.Range("A1:p100")

For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not UCase(.Value) Like "*[A-Z]*" Then
.Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell

Any help is ver much appreciated!!
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False


I have also tried (but it does not work when pasting):

Hi Norman! Sorry for the confusion, a bit stressed. .It works like this:

OK It works like this.

1) The user copies info from a program a paste it onto the spreadsheet.
2) the user presses a button
3) the macro is executed and calculations are presented

My problem is that when I run my macro that includes the sub that find and
remove blanks, Excel cannot make calculations. When I check the Excel
spreadsheet I see that the cells that have been affected by the sub
findAndRemoveBlanks have a small green marking. When choosing this marking
with my mouse I see that these cells are stored as text (the is what excel
says when I right click on these cells). I can then choose to convert these
cells into numbers. Thus I draw the conclusion that the macro somehow affect
the format of the cells.

After the user has pasted the info into the spreadsheet the format of the
cells is General. After having run the macro the format has changed.

I have also tried this code (that works better but with the same problem):

Private Sub findAndRemoveBlanks()
Cells.Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
end sub

please help me out if you know how to!

"Norman Jones" skrev:

Hi Sally,

Could you explain:

The contents of the spreadsheet are pasted onto the spreadsheet.
Therefore when running the macro the program stores all values as text

The macro merely romoves spaces from non-alpha cells and, for example,
coverts a text entry of 200 00 to a numeric 20000.

Incidentally, you should change:

.Replace What:=" ", Replacement:=""

to

.Replace What:=" ", Replacement:="", LookAt:=xlPart


---
Regards,
Norman



"Sally Mae" wrote in message
...
I have a problem with a macro. The macro searches a spreadsheet for
thousand
separator blanks and other uneccessary blanks. The contents of the
spreadsheet are pasted onto the spreadsheet. Therefore when running the
macro
the program stores all values as text, so I cannot use them in any
calculations. How do you solve this? I have tried recording a macro when
changing the format in excel but it does not work? Pleaase please help me
, I
really dont understand it!!!!!Thank you very much!!!

This is the code that I use in a sub for removing the blanks:
Dim WB As Workbook
Dim SH As Worksheet
Dim rng, rCell As Range
Set WB = ActiveWorkbook
Set SH = WB.Sheets("Beräkning")
Set rng = SH.UsedRange
'Set rng = SH.Range("A1:p100")

For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not UCase(.Value) Like "*[A-Z]*" Then
.Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell

Any help is ver much appreciated!!
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False


I have also tried (but it does not work when pasting):

Hi Sally,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng, rCell As Range
Set WB = ActiveWorkbook
Set SH = WB.Sheets("Beräkning")
Set rng = SH.UsedRange
'Set rng = SH.Range("A1:p100")

For Each rCell In rng.Cells
With rCell
.Select
If Not IsEmpty(.Value) Then
If Not UCase(.Value) Like "*[A-Z]*" Then
.NumberFormat = "0.00"
.Replace What:=" ", Replacement:="", LookAt:=xlPart
End If
End If
End With
Next rCell

End Sub
'<<=============


---
Regards,
Norman



"Sally Mae" wrote in message
...
Hi Norman! Sorry for the confusion, a bit stressed. .It works like this:

OK It works like this.

1) The user copies info from a program a paste it onto the spreadsheet.
2) the user presses a button
3) the macro is executed and calculations are presented

My problem is that when I run my macro that includes the sub that find and
remove blanks, Excel cannot make calculations. When I check the Excel
spreadsheet I see that the cells that have been affected by the sub
findAndRemoveBlanks have a small green marking. When choosing this marking
with my mouse I see that these cells are stored as text (the is what excel
says when I right click on these cells). I can then choose to convert
these
cells into numbers. Thus I draw the conclusion that the macro somehow
affect
the format of the cells.

After the user has pasted the info into the spreadsheet the format of the
cells is General. After having run the macro the format has changed.

I have also tried this code (that works better but with the same problem):

Private Sub findAndRemoveBlanks()
Cells.Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
end sub

please help me out if you know how to!

Thanks Norman but the code just does not work. Also it takes a very long time
to run and it does not remove the blanks. I think it is better to use the
code that I got from recording the macro. The code is:

Private Sub findAndRemoveBlanks()
Cells.Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

This code fixes the problem with the blanks but the other problem remains.
Please please please help me if you have any idea. I dont understand this and
it must be a fairly common problem since alot of people paste numbers into
Excel and the use these number for calculations. I am very thankful for any
help!


"Norman Jones" skrev:

Hi Sally,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng, rCell As Range
Set WB = ActiveWorkbook
Set SH = WB.Sheets("Beräkning")
Set rng = SH.UsedRange
'Set rng = SH.Range("A1:p100")

For Each rCell In rng.Cells
With rCell
.Select
If Not IsEmpty(.Value) Then
If Not UCase(.Value) Like "*[A-Z]*" Then
.NumberFormat = "0.00"
.Replace What:=" ", Replacement:="", LookAt:=xlPart
End If
End If
End With
Next rCell

End Sub
'<<=============


---
Regards,
Norman



"Sally Mae" wrote in message
...
Hi Norman! Sorry for the confusion, a bit stressed. .It works like this:

OK It works like this.

1) The user copies info from a program a paste it onto the spreadsheet.
2) the user presses a button
3) the macro is executed and calculations are presented

My problem is that when I run my macro that includes the sub that find and
remove blanks, Excel cannot make calculations. When I check the Excel
spreadsheet I see that the cells that have been affected by the sub
findAndRemoveBlanks have a small green marking. When choosing this marking
with my mouse I see that these cells are stored as text (the is what excel
says when I right click on these cells). I can then choose to convert
these
cells into numbers. Thus I draw the conclusion that the macro somehow
affect
the format of the cells.

After the user has pasted the info into the spreadsheet the format of the
cells is General. After having run the macro the format has changed.

I have also tried this code (that works better but with the same problem):

Private Sub findAndRemoveBlanks()
Cells.Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
end sub

please help me out if you know how to!

Hi Sally,

Thanks Norman but the code just does not work ... and it does
not remove the blanks

It works for me, but perhaps I do not understand your data.

Also it takes a very long time

There are ways to increase speed, but these would be pointless if the macro
does not funtion!

. I think it is better to use the
code that I got from recording the macro. The code is:

Private Sub findAndRemoveBlanks()
Cells.Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Your original request was to remove blanks from non-alpha cells.

How will your suggested code differentiate types of cell content.

If you wish, you may send me a sample of the data to be converted:

norman_jones@NOSPAMbtconnectDOTcom

(Delete "NOSPAM" and replace "DOT" with a full stop [period] )



---
Regards,
Norman