VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

I hope I don't need to give much description about the issue since the title and the code with an example file describes the issue very well.

Please Login or Register  to view this content.

Any suggestion how to overcome this issue?

Hi,

On Microsoft site is stated:

Returns a Characters object that represents a range of characters within the object text. You can use the Characters object to format characters within a text string.

There is also an example:

Please Login or Register  to view this content.

See for yourself at https://msdn.microsoft.com/en-us/lib.../ff198232.aspx

For what you want, you can try this:

Please Login or Register  to view this content.

Originally Posted by WouterM

For what you want, you can try this:

Please Login or Register  to view this content.

Using Left(),Right(), VBA.Replace(),w.Replace() and w.Substitute will remove the actual formatting of the Text. That's why I am very keen on using VBA Characters() function.

@WouterM

I came across that article aswell, but no where is it written that there is a limit of 260 Characters or so..

Originally Posted by karedog

The notorious SendKeys could be handy sometimes :

Thanks for your reply, I request you to go through the Post #3 content to know the actual need of VBA Characters() function.

Originally Posted by karedog

You are welcome, I request you try the code first, and see if that is qualified to solve the problem that can't be done by code on your first post.

Still you are missing my point. If you see in my attached example file in a1 cell the word "abc" is repeated in several times from that I have to identify the Bold abc part and delete the rest of the data by keeping all the words formatting as it is. Some of the abc's in different colors I want to keep all the formatting's of each words (bold,italic etc., of each words) which can be handled by VBA Characters() function alone (As far as I know).

Hope I made it clear.

In the old days there was a limit of 255 character
Nowadays the limit is 10,240 characters.

see: https://support.microsoft.com/en-us/kb/105416

I think the characters property of range has also this limit of 255 characters.

Originally Posted by WouterM

I think the characters property of range has also this limit of 255 characters.

Yes I agree but it's working upto 261 characters not matching with exact 255 characters condition too..

Originally Posted by WouterM

In the old days there was a limit of 255 character
Nowadays the limit is 10,240 characters.

Exactly.. But the 255 Characters limit is pretty old.. And Sixthsense is facing the issue after the 260th character..

Really thankful for your wonderful suggestion which really helps me in overcoming the excels character() function issue

Do you have any idea what I am doing wrong in the below code?

I would like to replace the bold abc part with "def" and after that I would like to copy paste the total content (Replaced Content) back to the same excel cell. At present it's pasting only the search text in excel. I am confused about what I am doing wrong

Please Login or Register  to view this content.

ReplaceAll Source:https://msdn.microsoft.com/en-us/lib...ice.11%29.aspx

After doing several testing the word object is also failing when it is called from excel.

Replacement happening nicely when the macro runs in word.

Please Login or Register  to view this content.

But the below code fails when it is called from excel and it's not doing the replacement task as expected and resulting the same input value as output.

Please Login or Register  to view this content.

Attached sample files with code for easy testing.

If you do late bind you must supply value for wdReplaceAll constant.

I received this below Ping Message from karedog after 3 hours from posting the Post #14 content

------------------------------------------------------------------------------------------------
Well, I am not a person that can take back his own words easily, and I've said that I won't post anymore in the thread, so here it is, using a Private Message.


1. The reason your code in post #13 failed is :

Please Login or Register  to view this content.

the "wdReplaceAll" constant is private to Word's environment, it is unknown to Excel's, so either substitute this directly with a number, or define it using const keyword like this :

Please Login or Register  to view this content.

2. The reason that you have to do manouver using tables object on post #14, is because the oRng is set as a whole document content, including extra carriage return (enter) :

Please Login or Register  to view this content.

So if this oRng is to be pasted back to Excel without resizing, the extra carriage return is also included.
The easy way to overcome this is by using selection object, first select first paragraph only, then copy this selection into clipboard, and then paste in Excel :

Please Login or Register  to view this content.

The complete code :

Please Login or Register  to view this content.

Hope this help.


Regards
------------------------------------------------------------------------------------------------

I would like to thank him for his extended supported via ping message too..

Originally Posted by Izandol

If you do late bind you must supply value for wdReplaceAll constant.

Yes it worked when it is made as

Please Login or Register  to view this content.

Thanks for fixing that issue

Originally Posted by Mandeep Baluja

[CODE]Range("B" & i).Value = Mid(Range("A" & i), Start, End1 - Start + 1)

Thanks for your suggestion, but please refer post #3 content.

I am facing issue in activesheet.paste line and the detailed code is given in the below link.

http://stackoverflow.com/questions/3...s-failed-error

Anyone knows which causes this error?