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

bvbull200

New Member
Joined
Jul 29, 2013
Messages
23
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
Joined
Jun 24, 2015
Messages
1,647
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
Joined
Jun 12, 2014
Messages
30,527
Office Version
365
Platform
Windows
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
Joined
Jul 29, 2013
Messages
23
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!!!
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
Holy crap, that worked perfectly!!! Thanks a ton.
You're very welcome...
 

bvbull200

New Member
Joined
Jul 29, 2013
Messages
23
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
Joined
Jun 24, 2015
Messages
1,647
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
Joined
Jul 29, 2013
Messages
23
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
Joined
Jun 24, 2015
Messages
1,647
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
 

Forum statistics

Threads
1,078,444
Messages
5,340,322
Members
399,368
Latest member
Sataman

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top