Text compatibility between Mac and Windows for VBA?

[Matt_O]

Hi Excel Gurus,

I have a 'find and replace' macro that works so long as I enter the text on my Windows machine but won't recognize the same text when it comes from a workbook created on a Mac.

For example, the macro replaces "I've" with "I have". If I highlight the word "I've" and re-type it the macro works as it should.

I copy all the worksheets from the source workbook (created on a Mac) to my macro enable workbook on my Windows machine. I've experimented with copying the target cells and pasting just text but still end up with the VBA code not unrecognized the text.

I plan to provide the macro enabled workbook to the Mac users for them to run the VBA on their machines. I'm not sure if this will be a concern in the future or not if they are opening the macro enabled workbook, copying the sheets over, and editing the date, and running the VBA all on a Mac. I just can't give them the macro to use if I can't confirm it works as it should.

Any ideas why this happens and/or a work around?

Thanks in advance,
Matt

 

[Matt_O]

Update:

'... VBA code not unrecognized the text.' should read 'VBA code not recognizing the text'.

I now see that the issue is the apostrophe. The Mac's is on a slight angle while the Windows version is straight up and down. Oddly, when I copy and paste the original text back into its cell the apostrophe remains at an angle. I tried a find and replace for any word with an apostrophe with a similar word that I typed into the 'Replace with' field and still no luck.

Matt

 

[Matt_O]

Problem solved!

For anyone encountering this issue be aware that sometimes text from a Word document may end up in Excel. The angled apostrophe is one such case. I was able to copy the text with the angled apostrophe and place them into VBA as part of the array of text I needed to search for.

Re-typing in the same text as another element in the array provided the straight up-and-down apostrophe I was expecting. My search now includes both versions of apostrophes and the routine performs the search and replace as expected.

Hope this helps someone in the future!

Matt