Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste numbers
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): |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste numbers
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): |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste numbers
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): |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste numbers
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste numbers
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste numbers
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste numbers
Thanks Norman! I have sent you en e-mail with my spreadsheet attached to it.
Please if you can take a look at it. I am getting desperate, no one seems to know what it is and I am confident that it is a very simple solution to this problem that has been keeping me busing for twom workdays (i am not a programmer....). Thank you ver much! "Norman Jones" skrev: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste numbers
Sorry forgot to mention that I do not know how the code I got from the macro
differentiate cells but it does work (as long as it works its fine)! So that is not a problem. The problem is the content becomes unuseful after the macro has been executed due to change of format. I think that the program does this because when excel reads the contents of the cells (the conent is pasted from another program, otherwise there is no problem) it does not recoginze it or something and then stores it as text. However it must be possible to reset the format as text in the macro. It is very easy to do in excel but i dont know how to do it in a macro. I does not work to record a macro for this since no code shows up.....Thanks for any help!! "Norman Jones" skrev: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste numbers
Hi Anders,
Your problem is that the blank spaces are not spaces but so-called non breaking space characters (chr(160)). I have therefore replaced the find and replace macro with: '============= Public Sub FindAndRemoveBlanks() ActiveSheet.UsedRange.Replace What:=Chr(160), _ Replacement:="", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False End Sub '<<============= I have sent you an updated workbook. --- Regards, Norman "Sally Mae" wrote in message ... Sorry forgot to mention that I do not know how the code I got from the macro differentiate cells but it does work (as long as it works its fine)! So that is not a problem. The problem is the content becomes unuseful after the macro has been executed due to change of format. I think that the program does this because when excel reads the contents of the cells (the conent is pasted from another program, otherwise there is no problem) it does not recoginze it or something and then stores it as text. However it must be possible to reset the format as text in the macro. It is very easy to do in excel but i dont know how to do it in a macro. I does not work to record a macro for this since no code shows up.....Thanks for any help!! "Norman Jones" skrev: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I cannot paste values as numbers | Excel Discussion (Misc queries) | |||
copy & paste numbers instead of formula | Excel Discussion (Misc queries) | |||
To convert calculated numbers for Cut & Paste | Excel Discussion (Misc queries) | |||
Paste Values not pasting numbers | Excel Discussion (Misc queries) |