Macro formula to display values in 2 columns based on other columns

[michael2]

Hi,

New to VBA. I have a large set of data that I need to go through. Now I am filtering multiple columns to see if it contains certain text, then entering value into 2 columns. So I'm looking for a macro that can search multiple columns for a pre-determined value and if present, then display texts in 2 columns. For example: If cells in column A contain "ADJ" then show "NO" in AB and "REASON" in AC & if cells in column S contain "CORP" then show "YES" in column AB and "REASON" in column AC. There are more columns that I need to search through but I'm not going to list them all so it'd be nice if I can apply this to maybe 4 columns.

I've found a code by @My Aswer Is This that works on one column but I'm not able to figure out how to edit the code so that I can use it on multiple columns. Maybe that it'll be a good foundation to what I'm looking for.

Thanks!

Sub Check_Column_Ac()
'Modified 2/3/2022 3:33:09 PM EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "AC").End(xlUp).Row

For i = 1 To Lastrow
If InStr(Cells(i, "AC"), "ECP") Then
Cells(i, "D").Value = "Valid"
Cells(i, "E").Value = "Correct"
End If

Next
Application.ScreenUpdating = True
End Sub

 

[myall_blues]

You can just add more conditions inside the IF....END IF part of the code, but take note of two things:
- It will only match the first true condition, so if column A contains ADJ then it will ignore whatever is in column S. If you need to account for combinations where both are true then the code needs to change.
- Using INSTR will match if whatever is in the cell contains the search string, so InStr(...,"ADJ") will match ADJ, ADJACENT and FADJE. Iyou want ADJ only change those lines to
If InStr(Cells(i,"A").Value="ADJ" Then

Sub Check_Column_Ac()
    'Modified 2/3/2022 3:33:09 PM EST
    Application.ScreenUpdating = False
    Dim i As Long
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count, "AC").End(xlUp).Row
    
    For i = 1 To Lastrow
        If InStr(Cells(i, "A"), "ADJ") Then         ' Do this if column A matches desired value
            Cells(i, "AB").Value = "NO"
            Cells(i, "AC").Value = "REASON"
        ElseIf InStr(Cells(i, "S"), "CORP") Then    ' Do this if column S matches desired value
            Cells(i, "AB").Value = "YES"
            Cells(i, "AC").Value = "REASON"
        End If
    Next
    Application.ScreenUpdating = True
End Sub

 

[michael2]

You can just add more conditions inside the IF....END IF part of the code, but take note of two things:
- It will only match the first true condition, so if column A contains ADJ then it will ignore whatever is in column S. If you need to account for combinations where both are true then the code needs to change.
- Using INSTR will match if whatever is in the cell contains the search string, so InStr(...,"ADJ") will match ADJ, ADJACENT and FADJE. Iyou want ADJ only change those lines to
If InStr(Cells(i,"A").Value="ADJ" Then

Sub Check_Column_Ac()
    'Modified 2/3/2022 3:33:09 PM EST
    Application.ScreenUpdating = False
    Dim i As Long
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count, "AC").End(xlUp).Row
   
    For i = 1 To Lastrow
        If InStr(Cells(i, "A"), "ADJ") Then         ' Do this if column A matches desired value
            Cells(i, "AB").Value = "NO"
            Cells(i, "AC").Value = "REASON"
        ElseIf InStr(Cells(i, "S"), "CORP") Then    ' Do this if column S matches desired value
            Cells(i, "AB").Value = "YES"
            Cells(i, "AC").Value = "REASON"
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Thanks. I actually have about 10 conditions now and I noticed one condition does not fully work, it only displays my values for half of the rows it should be. Any reason why that is happening? Is it because I have too many conditions?

 

[myall_blues]

I’d need to see your code and a screenshot of the offending area.