VBA - If a Cell Matches Another Cell, Select Entire Row

bvbull200

New Member
I have two sheets. On Sheet1, cell B3 is a value that can be changed based on user input. This value is the vendor code. On Sheet2 is an array of data for every vendor. Vendor name, vendor location, etc. Column B on Sheet2 contains the vendor codes.

What I am looking for is a macro so that Excel takes the vendor code entered in Sheet1 cell B3, then looks for it in column B on Sheet2. Once located, it should the select the entire row that the value appears in.

There will be other things done after that row is selected, but I can take care of that. I just need to get to where the correct row is selected.

Can anyone help?

I appreciate all input on the matter!
 

tonyyy

Well-known Member
bvbull200,

You might consider...

Code:
Sub SelectRow()
Dim vendor As Range, vendorRow As Long
Set vendor = Sheets("Sheet1").Range("B3")
vendorRow = Sheets("Sheet2").Columns(2).Find(What:=vendor, After:=Cells(1, 2), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
Sheets("Sheet2").Activate
Rows(vendorRow).Select
End Sub
Cheers,

tonyyy
 
Last edited:

Fluff

MrExcel MVP, Moderator
Hi
How about
Code:
Sub bvbull200()

    Dim VendorRw As Long

    If WorksheetFunction.CountIf(Sheets("MA").Columns(2), Sheets("MA (3)").Range("B3")) = 0 Then
        MsgBox "Vendor not found"
        Exit Sub
    End If
    VendorRw = Sheets("MA").Columns(2).Find(what:=Sheets("MA (3)").Range("B3"), LookIn:=xlFormulas, _
        lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row
        
    Sheets("MA").Activate
    Rows(VendorRw).Select

    
End Sub
You'll need to change sheet names to suit
 
Last edited:

bvbull200

New Member
bvbull200,

You might consider...

Code:
Sub SelectRow()
Dim vendor As Range, vendorRow As Long
Set vendor = Sheets("Sheet1").Range("B3")
vendorRow = Sheets("Sheet2").Columns(2).Find(What:=vendor, After:=Cells(1, 2), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
Sheets("Sheet2").Activate
Rows(vendorRow).Select
End Sub
Cheers,

tonyyy
Holy crap, that worked perfectly!!! Thanks a ton.

Hi
How about
Code:
Sub bvbull200()

    Dim VendorRw As Long

    If WorksheetFunction.CountIf(Sheets("MA").Columns(2), Sheets("MA (3)").Range("B3")) = 0 Then
        MsgBox "Vendor not found"
        Exit Sub
    End If
    VendorRw = Sheets("MA").Columns(2).Find(what:=Sheets("MA (3)").Range("B3"), LookIn:=xlFormulas, _
        lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row
        
    Sheets("MA").Activate
    Rows(VendorRw).Select

    
End Sub
You'll need to change sheet names to suit
Hmmm...I like the error check. Once I get the other functionality done, I'll revisit this. An error check could be helpful. Thanks a bunch!!!
 

bvbull200

New Member
You're very welcome...
I have this working for all cases where cell B3 is a match to a cell in column B on Sheet2. Thanks again for that. I have a new need now, though.

If the Sheet1 cell B3 does not match anything in column B of Sheet2, I need the entire Macro to stop. Even better if it displays a dialogue box saying something to the effect of "Vendor Number does not exist in database!"

I have noted in the code that you made exactly where I would need it to halt:

Dim vendor As Range, vendorRow As Long
Set vendor = Sheets("Sheet1").Range("B3")
vendorRow = Sheets("Sheet2").Columns(2).Find(What:=vendor, After:=Cells(1, 2), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
------------------------------------------BREAK HERE IF THERE IS NO MATCH-----------------------------
Sheets("Sheet2").Activate
Rows(vendorRow).Select

I hope what I'm asking makes sense.
 

tonyyy

Well-known Member
If the Sheet1 cell B3 does not match anything in column B of Sheet2, I need the entire Macro to stop. Even better if it displays a dialogue box saying something to the effect of "Vendor Number does not exist in database!"
Code:
Sub SelectRow()
Dim vendor As Range, vendorRow As Range
Set vendor = Sheets("Sheet1").Range("B3")
Set vendorRow = Sheets("Sheet2").Columns(2).Find(What:=vendor, After:=Cells(1, 2), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
If Not vendorRow Is Nothing Then
    Sheets("Sheet2").Activate
    Rows(vendorRow.Row).Select
Else
    MsgBox "Vendor Number " & Sheets("Sheet1").Range("B3") & " does not exist in database!"
End If
End Sub
 

bvbull200

New Member
Code:
Sub SelectRow()
Dim vendor As Range, vendorRow As Range
Set vendor = Sheets("Sheet1").Range("B3")
Set vendorRow = Sheets("Sheet2").Columns(2).Find(What:=vendor, After:=Cells(1, 2), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
If Not vendorRow Is Nothing Then
    Sheets("Sheet2").Activate
    Rows(vendorRow.Row).Select
Else
    MsgBox "Vendor Number " & Sheets("Sheet1").Range("B3") & " does not exist in database!"
End If
End Sub

This is very close. I have a lot of steps that are run after the "End If", though. I need those steps to not be run if there is no match. There is more, but basically it looks like this, now:

Code:
Dim vendor As Range, vendorRow As RangeSet vendor = Sheets("Vendor Info Edits").Range("B3")
Set vendorRow = Sheets("Database (DO NOT EDIT)").Columns(2).Find(What:=vendor, After:=Cells(1, 2), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
If Not vendorRow Is Nothing Then
    Sheets("Database (DO NOT EDIT)").Activate
    Rows(vendorRow.Row).Delete
Else
    MsgBox "Vendor Number " & Sheets("Vendor Info Edits").Range("B3") & " does not exist in database!"
End If
Sheets("Database (DO NOT EDIT)").Select
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
If you'll see, below the "End If" are additional instructions in the macro. Do I just need to move ALL of that under the "If Not" line of code? Basically, my new one will look like:

Code:
Dim vendor As Range, vendorRow As Range
Set vendor = Sheets("Vendor Info Edits").Range("B3")
Set vendorRow = Sheets("Database (DO NOT EDIT)").Columns(2).Find(What:=vendor, After:=Cells(1, 2), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
If Not vendorRow Is Nothing Then
    Sheets("Database (DO NOT EDIT)").Activate
    Rows(vendorRow.Row).Delete
Sheets("Database (DO NOT EDIT)").Select
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Else
    MsgBox "Vendor Number " & Sheets("Vendor Info Edits").Range("B3") & " does not exist in database!"
End If
Look right?
 

tonyyy

Well-known Member
Sorry, didn't realize you had additional code. Just add the line in red...

Code:
If Not vendorRow Is Nothing Then
    Sheets("Sheet2").Activate
    Rows(vendorRow.Row).Select
Else
    MsgBox "Vendor Number " & Sheets("Sheet1").Range("B3") & " does not exist in database!"
    [COLOR=#ff0000]Exit Sub[/COLOR]
End If
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top