CODEWITHSUNDEEP
excelvba
mohsin117
mohsin117

Reputation: 23

excel vba to search for a match

i have a date in Sheet1 A1 and on Sheet2 column A is filled with dates

im looking for something that when assigned to a button and pressed it will look down Sheet2 column A and if any of the dates match Sheet1 A1 it will bring a message box saying match found and if no match is found it will bring up a message box saying no match found

thank you

Upvotes: 1

Views: 111

Answers (2)

Romain Dag
Romain Dag

Reputation: 16

Try this and tell me 

Option Explicit

Private Sub CommandButton1_Click()

    Dim Sheet1 As Worksheet, Sheet2 As Worksheet
    Dim TheDate As String
    Dim c As Range
    Dim Last As Integer

    'Put your own name for your Worksheets
    Set Sheet1 = Worksheets("Feuil1")
    Set Sheet2 = Worksheets("Feuil2")

    'I put the value that you are looking for in the first Worksheets in cells B1
    TheDate = Sheet1.Range("B1").Value

    Last = Sheet2.Range("a65000").End(xlUp).Row

    'All your date are only in the columns A
    Set c = Sheet2.Range("A1:A" & Last).Find(TheDate, LookIn:=xlValues)


    If Not c Is Nothing Then
        MsgBox ("Found ! : " & c.Address)
        Set c = Nothing
    Else
        MsgBox "No match for : " & TheDate
    End If
End Sub

Upvotes: 0

Miras
Miras

Reputation: 106

You can use this code. But don't forget to change the name of the second worksheet if it is required.

Private Sub CommandButton1_Click()

    Dim cell As Variant
    Dim rangeSheet As Worksheet
    Set rangeSheet = Worksheets("Sheet2")
    Dim checker As Boolean
        checker = False

    Dim lastRow As Variant
        lastRow = rangeSheet.Cells(Rows.Count, "A").End(xlUp).Row

    For Each cell In rangeSheet.Range("A1:A" & lastRow)
        If cell.Value = ActiveCell.Value Then
            checker = True
            Exit For
        End If
    Next cell

    If checker = True Then
        MsgBox ("Found")
    Else
        MsgBox ("Not found")
    End If

End Sub

Upvotes: 1

Related Questions