Bdtjtk

I have only numbers in Sheet2 in col.B starting with B2: 81, 102 (in B3), 104 (in B4), etc, and in Sheet1 in col.A starting with A2, mixed text with numbes with no logical place text like: abc813bnm 12mn (in A2), fgh 81lkj 45ol (in A3), ert1042hji (in A4), and so on. I need to search each number from col.B/Sheet2 in col.A/Sheet1 and write in is an exact match on the same row in col.Q let's say. he exact match of first no.81 is in A3 (fgh 81lkj 45ol), but not in A2 (abc813bnm 12mn) where it is inside of 813 string. In my code 81 (and not only) it is "found" and in the cell with 81 and in a cell with 813, and I do not want that:

Sub SearchLCL()

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False



Dim LCL1 As String

Dim LCL2 As String

'Dim answer As String

Dim c As Range

Dim counter As Long

Dim totalLCL1 As Long

Dim totalLCL2 As Long



counter = 2



'Sheets("MailElibLCL").Select 'Sheet2

'Sheets("lucrari 2017").Select 'Sheet1



totalLCL2 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

totalLCL1 = Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row



'totalLCL2 = N

'totalLCL1 = N3



For I = 2 To totalLCL2

'answer = Worksheets("hedis1").Range("h" & counter).Value

LCL2 = Worksheets("Sheet1").Range("A" & counter).Value

'LCL2 = "=MID(Worksheets.Sheet1.Range(""A"" & counter),SEARCH(LCL1,Worksheets.Sheet1.Range(""A"" & counter)),LEN(LCL1))"

k = "Q" & counter

For j = 2 To totalLCL1

    LCL1 = Worksheets("Sheet2").Range("B" & j).Value



     If InStr(1, LCL2, LCL1, vbTextCompare) > 0 Then

    Debug.Print LCL1



'If LCL1 = LCL2 Then

        'If answer = "Yes" Then

            For Each c In Worksheets("Sheet1").Range(k)



                'c.Value = Mid(LCL2, Search(LCL1, LCL2), Len(LCL1))

                'c.Formula = "=MID(LCL2,INSTR(LCL1,LCL2),LEN(LCL1))"

                'c.EntireRow.Interior.Color = 6 ' Change the number to match the desired color.

                c.Value = LCL1 '& vbLf & Date   'Now (si ora minute secunde)

                'c.Interior.Color = 5296210 ' Change the number to match the desired color.



            Next c

        'End If

    'End If

    End If

    Next j

counter = counter + 1

Next I

'Else
'Call ScrieMailElib
'End If

On Error GoTo 0

Application.EnableEvents = True

Application.DisplayStatusBar = True

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

End Sub

Thank you.

I think you can just use a formula, this example is checking for 30 in abc30ded435wdfq345 for example

=IF(ISERROR(SEARCH(30,J16,1)),"No Match","Match")

Not sure if I fully understand your problem.

Perhaps you can work this into your code. It uses regular expressions. It's a surprisingly lengthy pattern to find a simple number.

This just looks for the number 81.

Sub x()



Dim oRgx As Object, rCell As Range



Set oRgx = CreateObject("VBScript.RegExp")



With oRgx

    .Global = True

    .Pattern = "([^0-9]|^)81([^0-9]|$)"

    For Each rCell In ActiveSheet.UsedRange

        If .Test(rCell) Then MsgBox rCell.Address

    Next rCell

End With



End Sub