VBA: Tweak code to use .replace with no character limitation?

Hi all,

I have this code (credits to @Fluff13) that replaces values on one worksheet with values on another (using a lookup value that is consistent across the two worksheets).
Basically, I am using this to translate content across multiple languages, where the lookup value = the language. See here for the initial thread.

Please Login or Register  to view this content.

If I use this with single values, e.g. replace "apple" with "Apfel", it works fine, however, as soon as I add whole sentences to replace, I get a "type mismatch" error on line

.Replace Ws.Cells(r, 2), Ws.Cells(r, c), xlPart, , False, , False, False

My assumption is that .replace cannot handle the replacement if the cell exceeds a certain character count.

My question is: is there a way to tweak this to allow me to replace full sentences, ideally lifting re character restriction completely?

Hope this works somehow?
Thanks all!

well first of all is your code right?

Please Login or Register  to view this content.

this says

https://docs.microsoft.com/en-us/off...place-function

Replace(expression, find, replace, [ start, [ count, [ compare ]]])

you need 3 arguments not optional at beginning. so your 'xlPart' argument is technically in the 'replace' part of what microsoft says. that is supposed to be replacement string not enumeration.

kingofcamden,

Range.Replace method has 255 character limitation, so perhaps...

Please Login or Register  to view this content.

thank you jindon. on microsoft page, it does not say limitation. dont know why?

omahaNative_1023,

You are talking about completely different "REPLACE".
You seem like someone with other username before.

what? what other replace? there is only one function. right? is 2 different ones? what username. you asking of my name?

At least 3 Replace under VBA, a function, a worksheet function and a method …

It is too hard without seeing actual data layouts.

If you upload a workbook with the correct layout, it will help.

Hey jindon,

Sure, sorry for not providing an example earlier.

I've re-attached the old file with the updated ranges. It's all dummy copy, but hopefully it helps?

Thanks so much!

Do you really have all those complete blank row(s) & column(s) around data range?

You also have "Example:" in C9.
Is this correct?

I do There's some other content there that I cannot move.

Oh, yes! Sorry. please ignore C9 - that was really just to say this is all example/dummy content.

Thank you!

Then try change to

Please Login or Register  to view this content.

Thank you! Tried this, but doesn't seem to work...there's no error message, but values don't get replaced.

Think I understand now that

[b2].CurrentRegion.Value is considered the 'start' point on the "Translations" worksheet and
[d9].CurrentRegion the 'start' point on the "Translator" worksheet.

However, if I compare to the old (working) code, I can see that

[d9].CurrentRegion actually 'started' one row up from the value (i.e. it was [b1].CurrentRegion in the old code, but the content didn't 'start' before cell b2).
So I tried changing [d9].CurrentRegion to [d8].CurrentRegion, but that also didn't work.

Saw you changed some of the values in this part:

Please Login or Register  to view this content.

Maybe it's connected to that? Tried to play around with this, but no luck...got a 'type mismatch' error most of the time.

Sorry for the hassle...appreciate your help!

Hey jindon,

Ahh, I think I did it! Basically just updated the following in the old code

[b1].CurrentRegion to be [d9].CurrentRegion

That seems to have done the trick

Please Login or Register  to view this content.

Yaaay! So happy with this! Thanks so much again for your help!
All the best!!

Oh, yes! Sorry. please ignore C9 - that was really just to say this is all example/dummy content.

Did you clear C9 before you run the code?

Hey jindon,

I didn't, but I just noticed that weirdly, the code needs a blank 9 row on "Translator" worksheet and a blank 2 row on "Translations" worksheet.
It doesn't seem to work if there is anything in those rows...

I now tried to copy everything over from the example spreadsheet to my master, but unfortunately, it doesn't work anymore...I can't tell why.
It's the exact same setup...all in the same cells, but I do have other content on those sheets...tried everything really. Also checked references, but they are the same across both sheets.
I haven't altered the code so it's so weird that it works on one worksheet, but not on the other... do you have any idea why? Are there any other cells that would need to be left blank?
Did a few spot checks, but no luck...

Thank you!

That's why I asked the question.

Unlike formula, getting correct data range is the one of the most important factor in the vba.

Ah, okay! Makes sense! Got it to work now

Basically, row 9 on "Translator" worksheet & row 2 and column A on "Translations" worksheet need to be blank for it to work

So happy it works now. Thanks for all the tips!!

Change my code posted in #13

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

The changed code is already tested here with your latest workbook.

I'm getting tired, so you need to upload the actual 2 worksheets you are working with.

Hey jindon,

So sorry about this! I played around with it all morning and think I know what it was...seems like more ranges need to be blank for it to work, i.e. no content is allowed to the left of the range that holds the lookup value on the 'Translator' tab.

Got it to work again! Thanks for all your help - appreciate it!