CODEWITHSUNDEEP
excelvbamacos
tmccar
tmccar

Reputation: 85

VBA sub to search Excel

I want to add a subroutine to a VBA macro which does a search for a value ("tag") in an Excel sheet "LB Rack", in range B2:B200. If the tag is found, I want it to run another sub, "InsertSVblock". If tag is not found, I want it to output a Msgbox message, and run sub "CheckforLBmatch(tag)", which searches another sheet.

My method is to do a VLOOKUP on the range, and check for error 1004.

It's working up to a point - if it doesn't find a match, it outputs the error message, and runs "CheckforLBmatch(tag)". But how can I get it to run "InsertSVblock", if a match is found? Or is there a better way to do this?

Public Sub CheckforLBmatch(tag)

Dim xlApp As excel.Application
Dim xlbook As excel.Workbook
Dim xlSht As excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlbook = GetObject("C:\07509\LB_RACKTMC.xlsx")
Set xlSht = xlbook.Sheets("LB RACK")
Set LBrng = xlSht.Range("B2:B200")

On Error GoTo ErrorHandler

       Debug.Print xlApp.WorksheetFunction.VLookup(tag, LBrng, 3, False)

Exit Sub

ErrorHandler:
If Err.Number = 1004 Then
    MsgBox "No SV component in LB Rack for " & tag
    Err.Clear
    CheckforET200match (tag)
    End If
  Resume Next

End Sub

Upvotes: 0

Views: 355

Answers (2)

SierraOscar
SierraOscar

Reputation: 17637

Just use the Range.Find() method. Based on your code I'm assuming you're calling this sub from a different Office application and you have set a reference to the Excel Objects library:

Sub CheckForLBMatch(tag As Variant)

Dim xlApp           As New Excel.Application
Dim xlBook          As Excel.Workbook
Dim xlSheet         As Excel.Worksheet
Dim xlSearchRange   As Excel.Range
Dim xlFoundRange    As Excel.Range

    Set xlBook = xlApp.Workbooks.Open("C:\07509\LB_RACKTMC.xlsx")
    Set xlSheet = xlBook.Sheets("LB RACK")
    Set xlSearchRange = xlSheet.Range("B2:B200")
    Set xlFoundRange = xlSearchRange.Find(tag)

    If xlFoundRange Is Nothing Then
        MsgBox "The value (" & CStr(tag) & ") could not be found!", vbOkOnly + vbInformation
        CheckForET200Match tag
    Else
        InsertSVblock
    End If

xlBook.Close False '// Optional [SaveChanges] argument

Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub

Upvotes: 1

Weasemunk
Weasemunk

Reputation: 455

When I started working with VBA, I found myself trying to accomplish all Excel functionality with Macros; however, that is sometimes less efficient than simply using Excel.

I would hardcode the VLOOKUP into the worksheet. That way you can separate your code into cases based on the value of the cell and test it much more easily. If IsNumeric(Cell.Value), run your subroutine, else handle the issue.

Upvotes: 0

Related Questions