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
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