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

bvbull200

New Member
Joined
Jul 29, 2013
Messages
24
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!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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:
Upvote 0
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:
Upvote 0
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!!!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top