VBA Extract all instances of a string within a range and write to cells

Hi all,

Another day, another VBA problem to solve Yaaay! Hope you can help me with this one...I'm just like

I have a range (C14 to C105 on tab 'Builder') that contains text in each cell. Each cell might contain one or multiple choice string(s) (see attached example - it won't let me post this as says it's HTML code?!)

On click of a button, I would like the script to

a) Check for any choice strings in each cell in the specified range (choice strings all start with {X, and end with })
Check a value in a specific cell on the 'Builder' tab for each choice string. E.g. If the choice string name is "choice1" it should check the value in cell E5 or if the choice string name is "choice2" it should check the value in cell E6 and so on
b) The script should then, based on the value it finds in E5 (or E6, depending on the choice string name), replace the full choice string with the relevant sub-string of that choice string...
c) ...and also replace the [X] in that sub-string with the value in E5 or E6.

Example:

1. Script finds choice string as part of text in a cell in range
2. Script checks for value in cell E5 (as choice1 would always lookup the value in E5) which is 5
3. Script finds relevant choice string bit (5 < first [X] coupons|) using the value (5) and deletes the rest of the choice string so only the following is left: first [X] coupons
4. Script replaces [X] in choice string with E5 value (5)
5. Final render should therefore be: first 5 coupons

I've attached an example file which might help. It's quite confusing, but would help me a lot with a massive translations project.
Any help appreciated...my brain is fried!

For the data provided.

Please Login or Register  to view this content.

It depends, so I said "For the data provided".

If you say, you have more than 2 choices, need to see how/where you present these conditions.

Sure, that makes sense. Actually, if I think about it, I really only ever need two different ones so your script already covers that

- I tried a few things to test if it takes the right sub-string in the main string so changed 5 < first [X] coupons| to 5 < first [X] coup5ons| to see what comes out (and if it would really take the 5 sub-string), but as soon as I execute, it just copies the whole cell (so does not replace anymore). The copy within each sub-string can change (sorry, should've mentioned that) so it should work regardless what text is in there: e.g. 5 < bla bla [X] text|
- also, is it possible to just replace within the same cell rather than copy/paste to column D?

Many thanks!!

If you want it to change the original data then
change

Please Login or Register  to view this content.

to just

Please Login or Register  to view this content.

will replace the data in the same cell.

Awesome, that works! Thanks!

Just saw that the .pattern has (first) and (coupon) hard coded:

Please Login or Register  to view this content.

Not sure if that can be changed to take the actual values (as they will change)?

Ah, ok...unfortunately I can't provide exact values to follow before and after the conditions as this can be completely random. The choice strings will be used wherever singular/plural changes (depending on the number)...so this can be, for example, 1 coupon / 2 coupons ... or 1 night / 2 nights ... or 1 car / 2 cars... that will depend completely on context. The pattern structure remains the same though:

Please Login or Register  to view this content.

In the above case there is one specific text for the value 0 and for value 1, specific text for all values between 2 and 4 and specific text for all values between 5 and 10 (or more).

It can also look like this though (depends on the translations how many choices there are per string and number):

Please Login or Register  to view this content.

Can this be achieved with VBA? I know it's extremely complex

Edit: Sure, no rush! Thanks for your help!!

I need to see it in a WORKBOOK, so I can see exact structure of the data in order to avoid unnecessary miss understandings.

Hi Jindon - tried to summarise in the attached. Hope this helps?

This is a block of choice1

{X,choice1,0#sit 0 amet|1#consectetur adipiscing elit |1 < sed do [X] eiusmod tempor|4 < incididunt [X] ut labore|5 < et dolore [X] magna aliqua|10 < Ut enim [X] ad minim veniam}

and you have 8 in E5 for choice1 value, but there is no 8.

What is the logic to conver above part to Ut enim 8 ad minim veniam?

And also how you convert the part of choice2 that you didn't give the result?

note: space added before/after the < due to the scarri blocking

Thanks so much for looking into this...super helpful!

For choice1:

- The idea was to keep the choice string short so in case the same translation can be used across multiple numbers, I wouldn't have to list each number, but would add e.g. 10<. This would then use the 10< sub-string for anything less than 10 down to the nearest number listed (in the example that would be 5< and below). So 10, 9, 8, 7 & 6 would use the 10< subs-string, but e.g. 5 would then use the 5< as that number has it's own substring. Hope this makes sense, but not sure if this actually works? If not, I can list each number individually (even though it would be repetitive), but would use # instead (so |4#...|5#...|6#...). Would that make things easier?

For choice2:

- both choice strings are part of the same sentence in the example (highlighted them in bold in the file). So it would render: Lorem ipsum dolor Ut enim 8 ad minim veniam(choice1) quis nostrud exercitation ullamco laboris commodo consequat(choice2) quis nostrud exercitation ullamco laboris.

Say, if choice1 substring is 1, E5, what is the result from the below?

{X,choice1,0#sit 0 amet|1#consectetur adipiscing elit |1 < sed do [X] eiusmod tempor|

I think this scenario would not happen as the |1 exists twice. It could be {X,choice1,0#sit 0 amet|1#consectetur adipiscing elit |2 < sed do [X] eiusmod tempor|, however, and it this case it would show as consectetur adipiscing elit if E5 is 1 and sed do 2 eiusmod tempor if E5 is 2. Hope this makes sense?

Hey, such thing is very important in coding, especially Pattern.

So the number may be followed by either "#" or "<" that's all ?

Ah, ok - gotcha! Yes, idea was to use # if there's 1 case for that single number and < if there is 1 case for multiple numbers. An example:

{X,choice1,0#sit 0 amet|1#consectetur adipiscing elit |3 < sed do [X] eiusmod tempor|

that would be 1 case for 0, 1 case for 1, 1 case for 2 and 3.

It's quite complex hah! My brain hurts.

Are saying that substring for each choice can be multiple?
like

choice1 1
choice1 5
choice2 2
choice2 5

etc?

Yes, that's correct! Each choice is treated separately, i.e. can have the same number sub-strings as another choice string in the same sentence.

Then I need to see the data, conditions and Exact results that you want in a workbook.

According to your explanation, your data and the result provided seems far away from the reality.
So I can not write a code without the detailed data/condition/result covering possible different situations.

Hi Jindon - Apologies for the back and forth. Now tried to summarise this in the file attached.

Note that I have removed the < logic completely now as I feel this would just make it too complex! So it's basically just 0#,1#,2#,3#... cases. However, there can (but must not) be up to 20 of these substrings + not all of them have to contain a [X] placeholder for the number.
Hope this helps?

You didn't give the case of multiple conditions for same choice...
So, this is only my guess when one or many choice have multiple sub strings.

Please Login or Register  to view this content.

Hi Jindon - so sorry for the delay! I'm sooooo impressed with this!! Seems to work I'll test it thoroughly over the next few days and let you know if I encounter any issues. Thanks so much for your help - have a great weekend ahead.

Hey Jindon - happy Friday! Hope all is well.

I had a bit of a play with the script and would like to ask you for a couple of favours:

1) Would it be possible to define 'choice1' / 'choice2' within the script rather pulling that value from the workbook?
Think this is this bit:

Please Login or Register  to view this content.

At the moment it seems to look for the "choice name" for choice1 in d5 and then takes the cell below for choice2? How can I define the names directly within the script for both choices without having to have these values somewhere in a cell?

2) Re the number values to pull in (in the example file that's cell E5 and E6) - how can I edit the cell location and tell the script to pull from cell x on sheet z for choice1 and value y on sheet z for choice 2? It might change where these values are placed in the workbook (cells and sheet) in the future so want to be sure I can edit the cells somewhere?

Other than that, it does exactly what I asked for so thank soooo much!!

Originally Posted by kingofcamden

1) Would it be possible to define 'choice1' / 'choice2' within the script rather pulling that value from the workbook?

I don't understand what you are trying to say.
According to the explanation so far, each choice have different substring(s), so how you determine which substring belongs to which choice?

Originally Posted by kingofcamden

2) Re the number values to pull in (in the example file that's cell E5 and E6) - how can I edit the cell location and tell the script to pull from cell x on sheet z for choice1 and value y on sheet z for choice 2? It might change where these values are placed in the workbook (cells and sheet) in the future so want to be sure I can edit the cells somewhere?

Can be done, but as I mentioned, fixing/adjusting code is not so simple, so I need a detailed example in a WORKBOOK.

Sorry for the confusion.

On 1) Basically, I want to be able to type e.g. x = Range("choice1").CurrentRegion.Value and y = Range("choice2").CurrentRegion.Value directly into the script rather than pulling that value from a cell. I will then define the choice strings manually depending on the names I added to the script.

On 2) I think the existing example workbook already has everything you would need? I basically just want to reference the number values - currently in cells E5 and E6 as well as the sheet they're on ("Sheet1") in the script so I can change the source cells in the future (in case the values need to move to a different cell). At the moment it looks like the script checks for choice string names (e.g. choice1 and choice2) as well as the number value (5,6 or whatsoever) all based on cell d5. Ideally, I want to define which sheet and cells the number values (5,6) should be pulled from for each of the choice names (choice1, choice2).

Hope this helps?

CurrentRegion refers to a consecutive cells either virtically / horizontally.

If I write a code without seeing your workbook, do you think you can adjust?

I don't want to do it again and again.

Sure, tried to show you what I mean in the file attached. Hope this works? Thanks for your help!

This is simply impossible.

How do you want the code to find proper substring for each choice?

Would it be possible if the name (choice1,choice2) as well as the values (5,6...) would be on a different sheet? In this case I could reference the cells on a setting spreadsheet and would just change that reference in case the cell changes. Hope that makes sense. So basically x = Range("d5").CurrentRegion.Value would link to different sheet (let's say Sheet2) and take the values from there?

Possible but again fully depends on how you present those conditions.

Hi !

Originally Posted by kingofcamden

So basically x = Range("d5").CurrentRegion.Value would link to different sheet (let's say Sheet2)

Just add any worksheet reference as for example V = [Sheet2!D5].CurrentRegion.Value2 …

Hi Jindon, hi Marc,

Thanks for the suggestion Marc - that's kind of what I am looking for, however, for both number choice names and values in the script (at the moment the numbers and choice2 is determined via cell d5, however, this should be individual values for me to edit in the script). Not quite sure if that can be done?

@Jindon: Does this file help? I moved both the choice names and the number values to a different sheet. Don't mind keeping your logic as long as I can change the sheet for the "d5" choice name?

Many thanks both!

Change to

Please Login or Register  to view this content.

Hi Jindon - perfect, thanks! It works if I only have a few cells (c14 down) to replace, but not for the full way down (c105)? I get a type mismatch error...sorry!

Originally Posted by jindon

Please Login or Register  to view this content.

Why did you change to b1?

Hi Jindon - ah, that did the trick! Thanks! I now transferred all of this into my actual document, but still get the type mismatch error It's the same cells and I think it might have to do with the text the choice strings were added to (it's HTML). Maybe some of the character used in the HTML are confusing the script? The error only shows up if I replace my HTML, not the example sentences we used. Which characters would the script have issues with if placed in the text? Tested adding random {} in the text, but that still works...HTML tags such as < > also no issue...hmmmm... I'll try to post an example file with text that works and text that doesn't. Just need to figure out what the issue is... Thanks so much for your help!

Try this one then and if this doesn't work, I need to see your data.

Please Login or Register  to view this content.

Try add one line in bold.

Please Login or Register  to view this content.

That did it!!! Awesome!! Soooo happy - thanks again for all your help. Finally cracked it I owe you.

No problem.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Done! Thanks again

Hi Jindon - hope all is well and soooo sorry for another question on this!

If I use one choice string multiple times in a cell, it looks like only the first instance is replaced.

E.g.

Same choice string, different sub-strings:

This is a test { X , Choice1 , 1 # [X] apple | 2 # [X] apples...12#[X] apples } bla bla { X , Choice1 , 1 # [X] banana | 2 # [X] bananas...12#[X] bananas }

> This is a test 1 apple bla bla { X , Choice1 , 1 # [X] banana | 2 # [X] bananas...12#[X] bananas }

Same choice string, same sub-strings:

This is a test { X , Choice1 , 1 # [X] apple | 2 # [X] apples...12#[X] apples } bla bla { X , Choice1 , 1 # [X] apple | 2 # [X] apples...12#[X] apples }

> This is a test 1 apple bla bla { X , Choice1 , 1 # [X] apple | 2 # [X] apples...12#[X] apples }

Do you know why this is the case? Can it loop until all choice strings are replaced?

Many thanks for your help!

I need to see both data and conditions in a workbook.

Hi Jindon - of course, sorry! There you go. Thanks for your help!

Of course, sorry - attached! Thanks!

Replace "test" sub procedure with the following code.

Please Login or Register  to view this content.

Change one line

Please Login or Register  to view this content.

That did it!!! Awesome - thanks once again! That should really be it. Have a fantastic weekend!

Hi Jindon - sorry, should'nt have said it's all working now (it was for the example). However, what changed now is that if the first instance of a sub-string contains the [X] placeholder (for the number), but the 2nd instance of that sub-string doesn't, it show's nothing when rendered.

Example

Choice string (1st instance with [X] - number is 1):

{X,Choice1,1#[X] apple|2#[X] apples|12#[X] apples

> should render 1 apple

Choice string (2nd instance without [X] - number is 1):

{X,Choice1,1#apple|2#[X] apples|12#[X] apples

> should render apple, but instead it replaces the sub-string with nothing (blank)

Attached the example file again with the desired output. This used to work before so the new addition to the code must have changed that?

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Yup, that did it! Thanks so much - super helpful!