Find String value in cell that has minor modifications to the end

Question

I wrote this code which is part of a Select Case statement. It searches for a term variable (find_Element) and then pastes it to another worksheet and another cell.

Case 1
        find_Element = "Finished goods, net"

                For k = 3 To last_WS
                    With Sheets(k).Range("1:200")
                    sh_Name = Worksheets(k).Name
                    'sh_Name = Replace(sh_Name, "_", " ")
                    Set pointer_to_Element = .Find(find_Element, _
                        .Cells(.Cells.Count), xlValues, xlWhole, xlByRows, _
                        xlNext, False)
                    pointer_Int_Row = CInt(pointer_to_Element.Row)

                    Worksheets(k).Cells(pointer_Int_Row, lock_Array(k)).Copy
                    Worksheets("EXTRACTIONS").Range("B12").Offset(, q).PasteSpecial xlPasteValues
                    Worksheets("EXTRACTIONS").Range("B11").Offset(, q).Value = sh_Name
                    q = q + 1
                End With
            Next k

q = 1

However many elements like this one have micro-variations in naming that render my code useless.

For example sometimes the worksheet instead of having Finished goods, net in the cell we are looking for it has Finished goods or Finished goods,....

How can i adjust my code to enable him to find the element and not be derailed by such micromodifications in the reports i am receiving?

look into [Levenshtein Distance](http://stackoverflow.com/questions/4243036/levenshtein-distance-in-excel) — Dmitry Pavliv, May 24 '14 at 19:56
Isn't there something that will just check if the first two words are existent instead of searching an exact match? This Levenshtein Distance looks very tough.... — Codo, May 24 '14 at 20:01
You could change `xlWhole` to `xlPart` and search for "finished goods", case insensitively. It depends how consistent the users are. — Andy G, May 24 '14 at 20:01

score 1 · Accepted Answer · answered May 24 '14 at 20:07

1

Change xlWhole to xlPart and search for "finished goods", case insensitively.

answered May 24 '14 at 20:07

Andy G

19,232
5
47
69

It works sorry for the late response i was out of office – Codo May 24 '14 at 22:08

Find String value in cell that has minor modifications to the end

1 Answers1