Non-Breaking Space

[cocolete]

Hello,

Recently I created a macro that runs pretty smoothly. However, I've encountered a problem where when we paste information the spaces are sometimes pastes as (hex 20) and sometimes it is pasted as (hex A0).

This has created a lot of problems because I basically paste info sent by clients into the spreadsheet and the extract information using a platform called Bloomberg Terminal Add ins.

Does anyone know of a way that I can ensure that spaces gets written and pasted using only the (hex20) regular space character?

As always thank you for your insights!!!!

[TMS]

Might help if you posted your code.

Regards

[shg]

Please Login or Register  to view this content.

[cocolete]

TMSchuks,

I apologize for not posting a code. In all honesty didn't have one. This concept was brought to my attention today and have been studying more on the subject.

I know it is very vague question I just wanted to know if codes can be written....which from your reply I now think they can....and just basically go in the right direction from there.

THANK YOU FOR YOUR PROMPT REPLY

[cocolete]

shg,

Is this code something that I would write in VBA and would be implemented in the whole workbook. Or do I have to write it in a module and incorporate it within a Macro?

THANK YOU

[cocolete]

PS:

The macro is very simple it is just a recorded macro where I copy and paste the info on sheet one and it will be "pasted" and formatted into another sheet.

I receive the info from lets say an "email" and my sheet just cleans the info up.

Module 5 and Module 7 run the codes for the submit unit or denomination trade

Module 5 is composed of module 6 (SubImpl3) and Module 8 (SubImpl5)

Module7: is composed of Module2 (SubImpl2) and Module 9 (sUBiMPL6)

[shg]

Now that you've posted your code, you're in very good hands with TMS.

[TMS]

@RS: I take it you've looked at the code ?

[TMS]

This routine will change non-breaking spaces to normal spaces. Call it after your current macro code.

Please Login or Register  to view this content.

It goes at the end of the Back2Back module:

Please Login or Register  to view this content.

This will make your macro execute more smoothly; it switches ScreenUpdating off and on so you don't see all the screen swaps.

Please Login or Register  to view this content.

Regards

[cocolete]

First of all guys thanks for your replies.

TMS...I already included the Application.ScreenUpdating into the sheets and WOW runs a lot more smoothly now...THANKS

The VBA code I'm sure it works but using a formula (StringToHex) I saw that the string of the "space" value that is causing problems is A0. I thought that it was a question of just changing the "What:=Chr(160)" with "What:=Chr(A0)" but that didn't work.

[TMS]

And your problem with A0 is .... ?

=CODE(MID(A2,10,1)) = 160

=StringToHex(MID(A2,10,1)) = A0

So, you're sure the VBA code works BUT ... ?


I think the point is to look at the character string before the code is run and the character string after it has run and see if you have normal spaces.

Regards

[cocolete]

hahaha sorry I swore it where 2 dif texts.

For example when I write that it doesn't eliminate the space:

48123YPV2*cc

[cocolete]

that * was actually the " " = A0 but pasted like that

[TMS]

Sorry, does the code do what you want or not? I'm not sure if you're askig a question.

Regards

[cocolete]

No it is not.

Sorry for the confusion

[TMS]

Not a question? OK.

If this has answered your question, please mark your thread as solved.

Regards

[cocolete]

Actually TMS, I changes the Sheet from Data to Inputs and there it worked.

Thank You ever so much.

[TMS]

You're welcome.

I won't ask what "I changes the Sheet from Data to Inputs " means ...

Regards