Matching values In Multiple Columns with VBA

January 14, 2015 at 12:47:15
Specs: Windows 8
Hello,
I am trying to create a macro that searches a sheet (Sheet 1, column AR) for a list of values found in column A of Sheet 3. After a match is found, I would like to compare the value from the row that has been found from column B in Sheet 3 to Column AP in Sheet 1. If it matches on both columns in the same row, I want it to copy the entire row from sheet 1 into sheet 2.

So far my code will accomplish the first part, but I have no idea how to make it compare the value in the second column with the row that was found. Any help would be greatly appreciated!
Here is my code: (I found most of the code in forums and then modified it a little bit)
Sub valueFinder()

Dim srchLen, gName, nxtRw As Integer
Dim lastName As Range, ff As String


'Clear Sheet 2 and Copy Column Headings
Sheets(2).Cells.ClearContents
Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column AR, copy it top the next row in Sheet2
With Sheets(1).Columns("AR")
For gName = 2 To srchLen

Set lastName = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
If Not lastName Is Nothing Then

ff = lastName.Address
Do
nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
lastName.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
Set lastName = .FindNext(lastName)
Loop Until lastName.Address = ff
End If
Next
End With
End Sub


See More: Matching values In Multiple Columns with VBA

Report •


#1
January 14, 2015 at 12:49:32
First, a posting tip:

Please click on the following line and read the instructions on how to post VBA code in this forum in a manner that will make it easier for us to read:

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 14, 2015 at 12:51:52
Sorry about that!

Sub valueFinder()
     
    Dim srchLen, gName, nxtRw As Integer
    Dim lastName As Range, ff As String
    
    
     'Clear Sheet 2 and Copy Column Headings
    Sheets(2).Cells.ClearContents
    Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
     'Determine length of Search Column from Sheet3
    srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
     'Loop through list in Sheet3, Column A. As each value is
     'found in Sheet1, Column AR, copy it top the next row in Sheet2
    With Sheets(1).Columns("AR")
        For gName = 2 To srchLen
         
            Set lastName = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
            If Not lastName Is Nothing Then
            
                    ff = lastName.Address
                    Do
                        nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
                        lastName.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
                        Set lastName = .FindNext(lastName)
                        Loop Until lastName.Address = ff
            End If
        Next
    End With
End Sub


Report •

#3
January 14, 2015 at 13:09:25
re: "but I have no idea how to make it compare the value in the second column with the row that was found."

Here are some tips that might help you add the instruction that you need:

When the .Find method is used, the variable that is Set contains all of the properties of the value that was found. In other words - in your case - VBA knows what Row lastName is in, what Column it is in, what its font settings are, etc.

Any valid property can be returned with the following syntax:

lastName.property desired

lastName.Value (the default)
lastName.Column
lastname.Interior.ColorIndex
etc.


Therefore, you can use lastName.Row as a argument in other instructions, e.g.

If Sheets(3).Range("B" & lastname.Row) = Sheets(1).Range("AP" & lastName.Row) Then

or

If Sheets(3).Cells(lastname.Row, "B") = Sheets(1).Cells(lastName.Row, "AP") Then

or

If Sheets(3).Cells(lastname.Row, 2) = Sheets(1).Cells(lastName.Row, 42) Then

I hope that helps!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 14, 2015 at 13:45:20
Thank you so much! That actually really does help in my understanding of this. I just am really unfamiliar with this syntax.

I inserted this line:

   If Sheets(3).Cells(lastName.Row, 2) = Sheets(1).Cells(lastName.Row, 42) And Not lastName Is Nothing Then

and am getting Runtime Error 91: Object variable or With block variable not set. If I remover the added second column comparison, it runs fine, so obviously I am violating the syntax somehow. Can you enlighten me? (I tried all three variations that you listed)

From Google research, I am guessing it doesn't like the fact that sometimes it is going to this comparison and lastName is nothing....do I need to use a nested if statement to make this work? I tried messing with those a little bit and just got error messages yelling at me.

Thank you again, your help is truly appreciated!


Report •

#5
January 14, 2015 at 15:11:23
Without testing your code (I don't have time to set up a sheet, etc.) the best I can do is suggest that you make sure that lastName is found before you test for lastName.Row.

None of the properties of lastName can be Set if lastName is Nothing. So, yes, I guess I'm agreeing that you should use a Nested If.

Have I suggested this tutorial to you before?

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question