VBA Help with .Replace & cell formatting

**kingofcamden** · 05-30-2019, 05:35 AM

Hi all,

Quite new to VBA and have spent all morning trying to solve a problem with the following script:

Please Login or Register  to view this content.

In summary, it replaces all placeholders (e.g. [NAME]) in range B14:B105 on the 'Builder' sheet with the actual value defined on the 'Conditions' sheet. Doing so, it looks up another value (languages) on the 'Builder' sheet to understand a) what to replace with and b) from what language column on the 'Conditions' tab (each column represents one language with the actual values for each placeholder listed below in rows).

So far so good - all working as expected. Now I have the issue that some of the cells (i.e. the actual values) on the 'Conditions' sheet contain formulas I need to use to format the cell for left to right languages (global settings of the Excel spreadsheet are right to left). Note: Builder!$E$7 contains the source value (in this case 5%). An example:

Please Login or Register  to view this content.

This renders (in the cell on the 'Conditions' tab) as %5 بنسبة(which is correct), however, when I execute the above script and it replaces the placeholder (in this case the placeholder is [%off]) with the actual value (should be %5 بنسبة), it seems to ignore the formatting set by the formula and pastes the value as 5%, which is not correct (as it pastes the value as right to left and not as left to right as defined on the 'Conditions' tab).

I believe that the following part of the script needs to change in order to replace the placeholders with the actual value (i.e. value as is, maybe rendered as text?) from the conditions tab.

Please Login or Register  to view this content.

Is there anything I can add to or change in SpecialCells(xlFormulas, xlErrors) to actually replace the value exactly as it is? Or any other idea? Tried all different xl...'s I could find on the net, but no luck so far (some even break the script).

Hope you can help?

**Norie** · 05-30-2019, 05:43 AM

If you replace values only that's all you'll get, you won't get any formatting.

What you need to do is, as well as replacing values, apply the appropriate formatting.

**kingofcamden** · 05-30-2019, 05:54 AM

Hi Norie,

Thanks for your response! Would I need to apply formatting for each case (and if so, how would I do that?) or is there something that I can add to copy/paste or apply the formatting from the source cell? Any help is appreciated.

Thanks!

**Norie** · 05-30-2019, 06:32 AM

To be honest I'm not sure.

It might be that you would have to change/revert to doing the find/replace cell by cell rather than using the Replace method for a whole range.

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

**kingofcamden** · 05-30-2019, 07:07 AM

Hey Norie,

Sure - thanks for helping out

Example attached. Hope this works?`

**kingofcamden** · 05-30-2019, 07:09 AM

...might be worth mentioning that the original file has more than 100 cells on the Builder sheet and more than 100 languages x 100 placeholders. So if there is any way to change the script to replace the placeholders AND keep the source formatting (of each cell), that would be super helpful...

**Norie** · 05-30-2019, 07:32 AM

Perhaps you could try something like this.

Please Login or Register  to view this content.

Instead of looping through the range on Conditions sheet it loops column B on the Builder sheet.

It first looks for the language in column B in row 2 on the Conditions sheet and it then looks up the value, e.g. {%off}, from column C in column B on the Conditions sheet.

If it finds a match for both language and value then it copies the value from the intersection on the Conditions sheet to the cell in column C on the Builder sheet.

Hope that kind of makes sense.

**kingofcamden** · 05-30-2019, 10:02 AM

Hey Norie,

Awesome! Thanks for this! Get the idea and like it. Unfortunately, it does not seem to work though, i.e. nothing happens when I execute the script? The ranges and lookup rows seem fine and it debugs ok, but the values don't get replaced. Any idea?

Thanks - I owe you a beer!

**kingofcamden** · 05-30-2019, 10:10 AM

PS: So it looks like if I change the value on the Builder tab to just "{%off}" (so not part of a sentence) it seems to find a match but then has an issue with

Please Login or Register  to view this content.

: error 1004 - copy method of range class failed.

So seems as if there are two issues: {%off} cannot be part of a sentence and the script then has problems copying/pasting. Not sure if the original "replace" function would work better in this case? Really just the problem of keeping the source format :-(

**Norie** · 05-30-2019, 10:37 AM

The code I posted only worked after I'd removed the merged cells in column C on the Builder sheet.

As for replacing values within sentences, that might pose another problem or at least prompt another approach.

Does your actual file data have merged cells?

Could you upload another workbook with more sample data, including examples with sentences/phrases?

**kingofcamden** · 05-30-2019, 10:56 AM

Ah, totally forgot about the merged cells! Yeah, think the replace function handled that nicely, just a shame that you cannot copy over the formatting at the same time. Maybe replace first, then replace formatting for all replaced cells?

Here's another example sheet - early stages, so don't have much more at the moment. Just know that it's going to be approx. 100 languages + 100 placeholders

Thanks for your help!

**Norie** · 05-30-2019, 04:16 PM

I think what might be needed is something to extract the all the {} placeholders from a sentence and then to the lookup/replace thing for each one.

**kingofcamden** · 05-31-2019, 05:17 AM

Morning Norie! Thanks so much for your help with this - script looks great!! Unfortunately, it doesn't seem to work for me...nothing happens. Also tried with just the placeholders, but that also didn't work. When I debug it shows me the following, which looks like the correct logic! Just wondering why nothing really happens

No merged cells or anything this time...

Please Login or Register  to view this content.

**Norie** · 05-31-2019, 05:44 AM

Sorry, nothing happens because I didn't have time to get round to make anything happen, sorry.

The file I attached is kind of a work in progress and I probably shouldn't have uploaded it.

If I get a chance later today I'll see if I can come up with something that actually does something.

**kingofcamden** · 05-31-2019, 06:13 AM

Hah

Cool, no worries. Really appreciate the help!! Still learning VBA so this super helpful for me! Thanks and have a good Friday!

**kingofcamden** · 06-04-2019, 11:43 AM

Hey Norie! Hope all is well. Is this something you could help with? Sorry, tried a few things myself, but no luck so far :-( Really like the approach you took, but not quite sure how to take it on from here :-( Many thanks.

**Norie** · 06-04-2019, 01:04 PM

Not had a chance to have a further look at this, hopefully get back to it tomorrow.

**kingofcamden** · 06-06-2019, 10:43 AM

Cool

Thanks!

**kingofcamden** · 06-11-2019, 05:45 AM

Hey Norie - hope all is well! Any chance you could still help with this? Thanks!

**Norie** · 06-12-2019, 07:09 PM

I've updated the previous file to do stuff rather than print debug messages.

Not sure if it works because to be honest I've forgot what's meant to happen.

Anyway, have a look at the workbook and see if it does anything near like what you are looking for.

If it doesn't I'll have a proper look at it tomorrow.:

**kingofcamden** · 06-14-2019, 12:14 PM

Hey Norie - thanks so much for your help again! Really appreciate it.

Just had a look at the file and unfortunately nothing happens when I run it - sorry! I can however see that the cells on the conditions tab are highlighted one after another if I run debug. But looks like it's then just pasting in the same cells rather than replacing the {} placeholders on the builder tab? Basically I would want all {} placeholders to be replaced pre language with the appropriate value in the format of the source cell. The initial script I had does replace everything well, however, does not copy/paste or apply the format of the source cell so e.g. arabic dates like 2019/01/31 showed up as 31/01/2019 etc.

I know...quite complicated. Been trying to solve this for month (very limited VBA knowledge here haha). Thanks!

**Norie** · 06-16-2019, 10:30 AM

Try replacing the sub ReplaceString with this.

Please Login or Register  to view this content.

**kingofcamden** · 06-17-2019, 10:11 AM

Hey Norie - thanks for this! Seems to work

Only thing is that it does not copy/paste the value as is and again seems to reformat on paste. All as expected for English, however, Arabic it renders 4% when it should render %4 (as per source cell). Interestingly, if I copy and paste that cell manually (values only) it's the correct value, but it seems to reformat when replacing the placeholder in the text

Not sure if there's a workaround for this...really weird! Thanks so much for your help with this - I owe you like 10 cyber beers!

**Norie** · 06-17-2019, 02:21 PM

Glad to see it's at least kind of working.

Could you upload a workbook with some more examples and/or a before/after/what you want to happen/what is happening type sheet etc.?

**kingofcamden** · 06-25-2019, 05:17 AM

Hey Norie! Hope all is well and so sorry for the super late reply. Think I managed to make this work! Looks like it wasn't your script (that's working fine), but the issue was the source and destination cell formatting. Turns out that if you apply RTL format to the destination cell and the source cell + format the content of the source cell using formulas to also show everything in RTL (%4 etc), it all works. If the destination cell has a LTR format and you replace placeholders with a RTL value, the value will be added as LTR as it's taking the destination cell format (so overwrites the format set in the source cell). Took me a while to figure out, but all working as expected now

YAAAY! Thanks so much again for your help! I owe you a six pack of cyber beer haha

VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Re: VBA Help with .Replace & cell formatting

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA Replace formatting on string within cell.

[SOLVED] Search &amp;amp; Replace Formatting for part of cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions

[SOLVED] Search & Replace Formatting for part of cell