PDA

View Full Version : [SOLVED] Change the macro to search for a specific word (as written) and not close to it



k0st4din
03-03-2014, 09:17 AM
Hello everyone.
I would like to ask you, can you help me (as it does not change this macro), but add something (I do not know what) so looking at my base data - specific words or phrases (L:L), not close to them, and then returns to me that their abbreviations are again based on my data (M: M), in all other sheets.
Sheet 2 (column L) - is my base data. In column M are my abbreviations, in column N is the price of my products .
In column L:L, I recorded attention - the possible spellings of our products, I want to say that it can be displayed in different ways ( or missed a letter or other arrangement ).
The question is in all other sheets to look for such a match and then return the abbreviation for a product.
- You might say - but the macro does exactly, what the problem is?
- Yes, it is, but some lines ( in all other sheets ) I returned several times to the same line my cuts . For this reason , looking for options or ask what to change to be able to look ! -> Precise words that were written by me.
LInk to file (http://www.sendspace.com/file/to3wxe) - In this example, everything is yellow shows where the error is.
You will see everything in the example that I attach, but here I can not explain in a few words:
abbreviation(Levo) - Levofloxacin Orange
Levofloksacin Orange
abbreviation(Levo500) - Levofloxacin Orange 500 br - > 'Checking in my database - macro is a word (a string of words) and I returned abbreviation - Levo + Levo500 - 'A must only be - Levo500
Levofloxacin Orange 500br
None of the above listed things is not the same (there is a space somewhere, another might have missed interval) - the idea is to look for -> EXACTLY WHAT I WANT
In each cell is a sentence which is also my target text after I found my macro returns its abbreviation. But the problem is that sometimes I returned abbreviation several times, which then confuses the other things in my file.
I sincerely hope you can understand what I need to help me.



Sub qw_Nere()
Dim X As Long, Cell As Range, CellText As String, WS As Worksheet
Dim Words As Variant, Replacements As Variant
Const TableSheetName As String = "Sheet2"
Application.Volatile
Words = Sheets(TableSheetName).Range("L1", Sheets(TableSheetName).Cells(Rows.Count, "L").End(xlUp))
Replacements = Sheets(TableSheetName).Range("N1", Sheets(TableSheetName).Cells(Rows.Count, "N").End(xlUp))
For Each WS In Worksheets
For Each Cell In WS.Range("H1", WS.Cells(Rows.Count, "H").End(xlUp))
CellText = ""
For X = 1 To UBound(Words)
If InStr(1, Cell.Value, Words(X, 1), vbTextCompare) Then CellText = CellText & "+" & Replacements(X, 1)
Next
Cell.Offset(, 7).Value = Mid(CellText, 2)
Next
Next
End Sub

westconn1
03-04-2014, 03:43 AM
you could try


If InStr(1, Cell.Value, Words(X, 1), vbTextCompare)

replace with

If Cell.Value = Words(X, 1)

k0st4din
03-04-2014, 10:01 AM
Hello and thank you warmly.
I think we're very close to the solution of my problem.
Please look at the picture or at the same attachment shown in yellow. The idea is to look for just such - absolutely want me to text but rather it seeks in this case - if you have only the word (text) without anything else. And then I add the + sign and add my next match.
Thank you again, hoping to deal with my problem.

k0st4din
03-13-2014, 10:22 PM
Hello to all
I would like to ask you would you can help me?
There's something to be added or changed to be able to detect the following abbreviations to words that I asked them to add a + sign.
If only one word I give exact result that I want, but if two or more do not. Show a small sample of my table to grasp my idea.

k0st4din
03-14-2014, 10:48 AM
Friends, I really need your help. There's something I do not do things correctly, I am not able to handle. There is something that obviously has to be touched for things to happen.

k0st4din
03-15-2014, 01:48 PM
Friends, if there is a way to fix this line in my macro?
Try waiting some answers and do not ask questions to other sites in the hope that you will be able to help me.

k0st4din
03-16-2014, 03:12 AM
Friends are not there any solution to this line in the macro? Ignited my head that I can not find a solution. If there is anything you do not understand in my inquiry, share with me so I can answer you.

k0st4din
03-17-2014, 09:59 PM
?

GTO
03-18-2014, 03:14 AM
Dear k0st4dtin,

I am overly tired as I write this; please excuse the brevity and bluntness. We are here to help. You have obviously expended great effort in your current code.

Those things said, I would humbly submit that the lack of responses are most likely for several reasons:


Your verbiage in post is unclear:

The question is in all other sheets to look for such a match and then return the abbreviation for a product.
- You might say - but the macro does exactly, what the problem is?
- Yes, it is, but some lines ( in all other sheets ) I returned several times to the same line my cuts . For this reason , looking for options or ask what to change to be able to look ! -> Precise words that were written by me.
abbreviation(Levo500) - Levofloxacin Orange 500 br - > 'Checking in my database - macro is a word (a string of words) and I returned abbreviation - Levo + Levo500 - 'A must only be - Levo500
Levofloxacin Orange 500br
None of the above listed things is not the same (there is a space somewhere, another might have missed interval) - the idea is to look for -> EXACTLY WHAT I WANT
In each cell is a sentence which is also my target text after I found my macro returns its abbreviation. But the problem is that sometimes I returned abbreviation several times, which then confuses the other things in my file.


In the supplied workbook, stuff (Procedures) are named like "Terapia_Nereimburs", which does not easily translate.


Whilst normally, I would encourage "include more (code, the workbook, etc.)", in this case, I would suggest simplifying. In gist - show us a value that is being 'missed', or 'caught' multiple times when it should only be 'caught' once.

While I am not confident, I hope that helps. I appreciate that you are trying your best to get help.

Mark