CODEWITHSUNDEEP
vbaexcel
Joel
Joel

Reputation: 103

How to match/search the value of Sheet1.Range("A1") in Sheet2.Range("A1:A10") in excel VBA

Let's say i have this value "ABC123" in Sheet1.Range("A1")

I want to search for/ match this value in Sheet2.Range("A1:A10") // or the column

If the value is found
      //msgbox "Found"
else
     //msgbox "Not found"
end if

Upvotes: 1

Views: 971

Answers (5)

AHMED FARiD
AHMED FARiD

Reputation: 1

Sub foo2()
    Dim t As Long
    On Error GoTo NotFound
    t = Application.WorksheetFunction.Match(Worksheets("Sheet1").Range("A1"), Worksheets("Sheet2").Range("A:A"), 0)
    MsgBox "Found
    Exit Sub      
NotFound:
    MsgBox "Not found"
End Sub

Upvotes: -2

user3598756
user3598756

Reputation: 29421

just playing around a little bit with Scott's solution:

Sub foo2()
    Dim t As Long
    On Error GoTo NotFound
    t = Application.WorksheetFunction.Match(Worksheets("Sheet1").Range("A1"), Worksheets("Sheet2").Range("A:A"), 0)
    MsgBox "Found
    Exit Sub      
NotFound:
    MsgBox "Not found"
End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Sub dural()
    Dim s As String, r1 As Range, r2 As Range, r3 As Range

    Set r1 = Sheet1.Range("A1")
    Set r2 = Sheet2.Range("A1:A10")
    s = r1.Value

    Set r3 = r2.Find(what:=s, after:=r2(1))
    If r3 Is Nothing Then
        MsgBox "not found"
    Else
        MsgBox "Found"
    End If
End Sub

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33682

Try using the Match function below (you will get the row number found):

Option Explicit

Sub MatchTest()

Dim MatchRes As Variant

MatchRes = Application.Match(Worksheets("Sheet1").Range("A1").Value, Worksheets("Sheet2").Range("A1:A10"), 0)
If IsError(MatchRes) Then
    MsgBox "Not found"
Else
    MsgBox "Found at row " & MatchRes
End If

End Sub

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

try this:

Sub foo()
    Dim t As Long
        On Error Resume Next
        t = Application.WorksheetFunction.Match(Worksheets("Sheet1").Range("A1"), Worksheets("Sheet2").Range("A:A"), 0)
        On Error GoTo 0
        If t > 0 Then
            MsgBox "Found"
        Else
            MsgBox "Not found"
        End If
End Sub

Upvotes: 1

Related Questions