VBA Code to match 1 column to two others and provide corresponding results based on match results?

CarrieB

New Member
Joined
Sep 25, 2014
Messages
3
First time poster here! I had this until I got to the second condition, now it looks like I need VBA and I'm not as well versed with that. Ok - Not versed at all, a total googler, LOL. I need to search column F and see if the contents match either column P or column S. If yes for P then enter "text a " in cell, if yes for S then enter "text b" in cell, if false leave cell blank... this goes down the entire column N. The formula I had for matching column F and P worked which was this... =IFERROR(IF(MATCH($F4,$P$3:$P$160,0),"text a",),""). I tried several different things in VBA but they were all broken one way or another and I was not able to fix them. I appreciate any help.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,437
Office Version
  1. 2013
Platform
  1. Windows
Hi CarrieB, welcome to MrExcel Forum
Your formula indicates that you want to check each cell of column F to determine if there is a match anywhere in column P or column S. Not just on the corresponding row to the cell in column F. That is what this macro will do. The problem is that you did not specify which column you want the "text a" or "text b" in. I chose to use column T and you can change that in the code very easily to the correct column. See notations.
Code:
Sub matchF()
Dim sh As Worksheet, lr As Long, c As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, "F").End(xlUp).Row
    With sh
        For Each c In .Range("F2:F" & lr)
            If Application.CountIf(.Range("P:P"), c.Value) > 0 Then
                .Range("T" & c.Row) = "text a"   'Change "T" to appropriate column
            ElseIf Application.CountIf(.Range("S:S"), c.Value) > 0 Then
                .Range("T" & c.Row) = "text b"    'Change "T" to appropriate column
            End If
        Next
    End With
End Sub
 
Last edited:

CarrieB

New Member
Joined
Sep 25, 2014
Messages
3
Awesome! It works perfect. I doubt I would've come up with this, I did not come close. I spent hours trying to figure it out but in the end, you don't know what you don't know! Thank you so much.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,437
Office Version
  1. 2013
Platform
  1. Windows
Awesome! It works perfect. I doubt I would've come up with this, I did not come close. I spent hours trying to figure it out but in the end, you don't know what you don't know! Thank you so much.

You're welcome,
Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,177
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top