Highlighting Text through finding strings

[dluhut]

Hello Excel VBA experts,

I have a question on how to use MID and InStr function in VBA that looks for string that I want as well as the quantity which normally would be "beside" it. Below are examples of it.

e.g.1 ... ... ... 6 1234 phones ... ... ... 2 9876phones ... ... ...
e.g.2 ... ... ... 30 - 12345678 ... ... ... 1 - 87654321 ... ... ...
e.g.3 ... ... ... 11 x 5555 IP Phones ... ... ... 8 x 5454 Phones ... ... ...

As you can see, due to individuals human entry, individuals have their own method of inputs. On e.g.1 for example, he/she forgot to put an extra space on in between "9876phones". Also, on e.g.3, sometimes there's a word "IP" before the word "phones" on it.

Notice too that, the quantities are normally to the left side of the model number of phones (1234, 9876, 5555, 5454) or it's phone ID (12345678 [id phone for 1234] or 87654321 [id phone for 9876]), sometimes separated by a 'space', dash ' - ' and 'x'.

My question is, how would I be able to highlight the quantities and the model number phones and/or phone ID when the model number of phones or it's phone ID are found in the text?

[gmk]

@dluhut

Do you mean something like this?

To make this work put your strings on sheet 1 starting at D4. Adapt code where marked to suit.

Please Login or Register  to view this content.

hth

gmk




Please remember to click the * below the post to say thanks and mark the thread as solved if answered satisfactorily.

[dluhut]

Hi gmk,

Thanks for helping out! Honestly speaking, I don't know what this code is doing. However, it seems that it doesn't highlight the quantity as well as the model phone and/or phone ID.

I've edited my original post, so that people can see what to highlight.

[smuzoen]

Could you supply a sample workbook rather than text in the post otherwise we have to reconstruct the workbook - it is better to supply a sample workbook with no sensitive data - select Go Advanced, Manage Attachments and upload a sample workbook - in the sample workbook show how the data is currently set out and how it should appear after the code is run. That way it will be a lot easier to supply a solution for you.

[dluhut]

Hi smuzoen,

Will do, and have attached it.

[smuzoen]

Am I correct that to start off there is NO colour coding at all - that is that the entire text is black? Then you want to have the quantity in blue, and model number in red?

---------- Post added at 11:59 PM ---------- Previous post was at 11:54 PM ----------

As well do you have a MASTER LIST of Phone models available - if you did then it would probably make the job easier by searching for particular strings. The logic is fairly complex due to the large number of variations in data entry. I would seriously consider automating the data entry by the use of forms so that every entry into the worksheet is in the same format and removes the individual variation that will occur by direct entry of data into the worksheet. Allowing users to free type data entry is fraught with problems as you obviously know.

[dluhut]

Hi Smuzoen,

Yes, you are correct. to start off, there is NO colour coding at all, and yes again, I would like to have the quantity in blue and model number in red.

As for the MASTER LIST of phone models, I don't have, but the "gist" of it are those. I believe, with this sample, I could further "expand" the code going forward if there's more phones that needed to keep track of.

I completely agree with you totally smuzoen, to have a form type so that user can just "choose and click". However, that project is not the priority of the IT people

[smuzoen]

I could write some code for you however there will be a lot of rules/logic - the best method I think would be to use regular expressions to try and achieve what you require. If you have not used regular expressions before then they can be a little tricky until you get used to using them. That being said to knock up a form to do the data entry is not difficult. If you want I could make a sample form for you - they are not difficult to do and if you have some VBA knowledge you would be able to expand on that. If you give me to tomorrow I will design a form for you showing you how to automate the data entry and in the same workbook I will give you a macro using regular expressions that hopefully will achieve what you need. It is 1am in Australia so if you can wait until tomorrow I will upload a sample workbook with a macro to try and do the colour coding and in that workbook I will put in a form to automate data entry. Forms are not difficult to do and the beauty of them is that the data can be validated and there will be consistency in data entry. In the long run that is really the solution you need rather than code to fix up free typing. So if you can give me until tomorrow I will put something together for you.

[dluhut]

Greatly appreciated smuzoen!!! I shall try and use that sample and push it up to the higher ups.

[smuzoen]

Hi there
I have some code for you that should do what you want based on the sample you sent me. I have attached a workbook - On the Approved sheet is the text as you sent me however it is all in Black font. On the Sample Output sheet is how you wanted it to appear. I have place a button on the Approved sheet to run the code to do the colour coding/add spaces where necessary etc. I also have as promised placed a Testing Worksheet that has a button that will bring up a sample form showing you how this problem would be best addressed moving forward. You will see you cannot enter text into form where only numbers are required and I included an example drop down list that has a few sample device types. It is just to show you how I think the problem should be addressed ultimately. The code is in the user form showing how you could approach the problem. It will also do the necessary colour coding as well so have a play with that - all the data you enter into the form is written to the Testing Sheet.
The code for fixing the Admin comments may have to be extended as I worked with the sample you sent however hopefully you should be able to make the necessary changes. If you need help with additional requirements let me know. The code for fixing the admin comments is as follows however it is also in the workbook in Module 1.

Please Login or Register  to view this content.

Have a play with the attached workbook. Before you run this on real data make sure you do a backup as you cannot undo changes made by VBA. If you have any problems or questions let me know.

[jindon]

This should do

Please Login or Register  to view this content.

[dluhut]

Smuzoen, Jindon,

Thank you so much for your contribution!!! I've checked the file that both of you have attached, and both works properly on the sample.

I tried to copy and paste the 'admin comments' that i have on the original file, and it works fine as well...especially yours Jindon.

As for Smuzoen, it gave me an error which says 'Error 9 (Subscript out of range) in procedure colCodeStr of Module Module1.

That being said, to both experts, Smuzoen and Jindon, Since I have to calculate the total number of rebate phones for each model, I would be then creating columns for each model and to put the quantities in each of the respective column. How would I do that in the code. Both of your codes are too complex for me to understand. Below is an example.

In column B, I would have 5312 Phones which is the phone ID 50006122. So, any quantities under 5312 Phones (50006122), would then be put on column B, i.e. on the attached spreadsheet, quantities of 44 on B2, quantities of 30 on B3 etc.
In column C, I would have 5320 Phones which is the phone ID 50006123. So, quantities of 6 would be on C2, and quantities of 3 would be on C3.
...
...
...

Also, to comment on the form smuzoen, it is indeed a great idea. But it's not the kind of form that I would want. Reason being, as you can see from the admin comments, there's a minimum of at least 1 phone to x numbers of phones in a single comment for rebates. Thus, would it be possible to have headers of "phones", "quantities and "price" across the columns, and phone types (with phone id in brackets) across the rows....this way, individuals could just click on the radio button and then put in the quantities and price for any phones that are applicable for them for rebates.

Once again!!! THANK YOU so much to both Smuzoen and Jindon for your help!!! It definitely help me in my work.

[jindon]

Can you upload the file with your desired result?

Just curious, I haven't got email notice for this thread and even subscription doesn't show me this thread.

And more I can not chase this thread.... something is wrong....