Cell.Characters(<StartPosition>,<LengthOfOldText>).Text = <NewText> occasionally not working

[Dodgester2]

Hello group,

Looking for why the following line doesn't work as expected on occasion (probably about 15% of the time)

l_rngCurVerse.Characters(l_lngStartPositionOfOldAnnotation, l_lngLengthOfOldAnnotation).Text = l_strCurrentVerseAnnotation




I can see all of the variables in this particular are returning correct values

l_rngCurVerse is referencing the correct cell

l_lngStartPositionOfOldAnnotation is showing the correct start position

l_lngLengthOfOldAnnotation is showing the correct length of the old annotation text

l_rngCurVerse.Characters(l_lngStartPositionOfOldAnnotation, l_lngLengthOfOldAnnotation).Text is showing the correct text that the line of code is supposed to be replacing

l_strCurrentVerseAnnotation is showing the correct new text that is to be annotated


There is no lock on the cell and the worksheet is not protected, but then this shouldn't be an issue anyhow given this line of code does work about 85% of the time, but it is still frustrating it doesn't work for the other 15% of the time as it also doesn't error out, thus easy to overlook.


What's the purpose of using this code?

Given the format, if I use the plain Find and Replace, it will mess up the format of the cells and that would be a lot of work, thus that's what had me think of this method to overcome that issue.

Why not taking it into something like MS Word?

There's way too many cells with text to do that with, so that's why using VBA seems to be much more simple to do than the copy and paste method others have stated.

Wish I could say I have seen a pattern of what seems to be causing this intermittent issue, but I haven't noticed a pattern. I know, I know, it makes it much harder without that pattern, but then if I would have spotted a pattern, I probably would have been attacking the issue based on that pattern.

 

[Dodgester2]

I added the "l_rngCurVerse.Characters" object to the watch, and when I had it break on the line, noticed the Text property on it came back as "<Unable to get the Text property of the Characters class>" Again, this doesn't happen in every occurrence, but when it does, this is what I get. According to one post I saw, the length of the text is the issue, so guess I will be doing some tracking in that regard though no cell reaching the 1,024 character count mark, as the highest I have is 544 characters in a single cell.

 

[Rick Rothstein]

According to one post I saw, the length of the text is the issue, so guess I will be doing some tracking in that regard though no cell reaching the 1,024 character count mark, as the highest I have is 544 characters in a single cell.

The problem is the Characters property... its Text property can only be assigned text that is less than 256 characters in length.

 

[Dodgester2]

Yeah, I kinda figured that out as it seems it can only work when there's up to 255 characters. However, I need something that is going to work with more than 255 characters in the cell without changing the format of the text in the cell, thus why the Find & Replace method doesn't work. I had thoughts of splitting the information into multiple cells, but that won't retain the format of the text either.

It makes me think of when I had the issue of more than 65,536 defined names within a single workbook that was saved that way and closed out, the next time the workbook was opened, it would bring up Dr Watson, which then it would remove EVERYTHING except for the data, formulas, and default format; including charts, VBA codes, defined names, all formats (except default format). That previous issue applies to Excel 97, 2000, 2002, and 2003.