VBA not working correctly

[HuskerBronco]

I have a macro in my workbook that cleans, and sorts a paste from another workbook.

Part of this macro is supposed to remove any CHAR(32) and replace it with a space.
After running the macro, there are a few cells with CHAR(32) remaining.

For example, a CODE check on one particular cell reveals the following:
(Reading cell from RIGHT to LEFT) : 101....116....97.... 116....83.... 32....111....103....101....105....68.... 32....110....97....83

If I try to EDIT the cell, and REPLACE [Find what: CHAR(32) ] with [Replace with:" "] --- Excel tells me that it cannot find a match.
(And...no, using Chr(32) in the [Find what:] field, doesn't work, either).

Here is the section of the macro that is supposed to remove CHAR(32):

Range("A83:C168").Select
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Selection.Replace What:=Chr(32), Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
On Error Resume Next
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


There are three other incidents that are removed with similar code within the same macro:.....CHAR(160)..." U"...and "(N)".
Each of these incidents are successfully removed.

Clearly, there are TWO occurrences of CHAR(32) in the cell mentioned, above.
Why isn't Excel recognizing these characters...??....

[JR@SGC]

Hi


have you tried this?

Replace What:="32"

[HuskerBronco]

Hello, JR

That's a formula that I lifted from an Excel help website.

Do I need to change it to CHAR.....???

The Chr designation works to remove the OTHER instances.



I will switch it...and see if that solves my problem.

[HuskerBronco]

Didn't work...JR

Also.....in response to:....... [ Have you tried this? Replace What:="32" ]
That would merely eliminate the NUMBER 32...wouldn't it??.......... (Yes...I just tried it).

This isn't making any sense to me.

[Bryan Hessey]

Didn't work...JR

Also.....in response to:....... [ Have you tried this? Replace What:="32" ]
That would merely eliminate the NUMBER 32...wouldn't it??.......... (Yes...I just tried it).

This isn't making any sense to me.

Hi, just curious, isn't char(32) a space? - - so ".Replace What:=Chr(32), Replacement:=" ", _" would seem to be self-defeating. Can I presume that the Char(32) was replaced with "" and the gap shown was a web thing?

---

[JR@SGC]

hmm

i got it to work with the below:

Selection.Replace What:=Chr(32), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

but char(32) is a space, are you sure want rid of them all?
Also you put a space between your "" is that a typo? If not you would be replacing a space with a space

Your code:

Selection.Replace What:=Chr(32), Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

[HuskerBronco]

Yep...CHAR(32) is, indeed, a space......in HTML.

When I try to MATCH cells in my worksheet with HTML coded cells, they don't match.

I just figured out why.

I don't need to replace ALL CHAR(32)...just the ones on the far left and far right of the text string.
This should make my cells match.

How do I write the VBA to achieve this...???

[Bryan Hessey]

Yep...CHAR(32) is, indeed, a space......in HTML.

When I try to MATCH cells in my worksheet with HTML coded cells, they don't match.

I just figured out why.

I don't need to replace ALL CHAR(32)...just the ones on the far left and far right of the text string.
This should make my cells match.

How do I write the VBA to achieve this...???

Use the Trim - see help on List of Worksheet Functions Available to Visual Basic

but my point before was that you were replacing char(32) with space
Selection.Replace What:=Chr(32), Replacement:=" ", _ - but yes, you need to NOT replace all, just leading & trailing

2:20am, back later.

Please Login or Register  to view this content.

will work

---

[HuskerBronco]

Thanks a bunch, guys.

I already had the trim function included in the macro.
I was overriding it with my code to replace Chr(32) that needn't be removed.
The macro functions perfectly, now.

However....yesterday, I posted a question pertaining to the same macro in this forum.
No one has responded to my post in the past 24 hrs.

Maybe you could take a look at it...??


http://www.excelforum.com/showthread.php?t=576818