CODEWITHSUNDEEP
ms-accessvba
vuyy1182
vuyy1182

Reputation: 1686

How can I find specified data in a excel row using access vba

I'm trying to find specified data in a excel row. Like from row range A1:A1J, only one cell is having data "Process" . if i find that data in a given range, i need to pop up the message "Found" Here is my code 

         Dim ExcelApp As New Excel.Application
         Dim ExcelBook As New Excel.Workbook
         Dim rng As Excel.Range
         Dim rngDefine As Excel.Range

         Set ExcelBook = ExcelApp.Workbooks.Open("C\temp\find.xlsm")
         ExcelApp.Visible = False       
        'Define your own Range
         Set rngDefine = ExcelBook.Worksheets("Datatab").Range("A1:AJ1")

         'ExcelBook.Worksheets("Datatab").Range ("A1:AJ1")

         Set c = .Find("Process", LookIn:=xlValues)
         For Each rng In rngDefine
         If c = "Process" Then
         MsgBox "Found"
         End If
           Next

         ExcelApp.Quit
         Set ExcelApp = Nothing

Not working. Any furthur code i need to add?

Upvotes: 0

Views: 100

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

First approach (slighlty faster):

Set rngDefine = ExcelBook.Worksheets("Datatab").Range("A1:AJ1")

If IsError(ExcelApp.Match("Process", rngDefine, 0)) Then
    MsgBox "Not found"
Else
    MsgBox "Found"
End If

Second approach:

Dim c As Excel.Range

Set rngDefine = ExcelBook.Worksheets("Datatab").Range("A1:AJ1")

Set c = rngDefine.Find(What:="Process", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

If c Is Nothing Then
    MsgBox "Not found"
Else
    MsgBox "Found"
End If

Upvotes: 2

Related Questions