Macro formula to find value in a column and if that criteria is met add specific values in others cells in that referenced column

[Lowbob131]

Hello,

I am relatively new to VBA so excuse my ignorance. I would like to create a macro that looks in a column (AC) in each cell and finds if that cells contains a specific value ("ECP") within a larger text (e.g. "WEWEV12321 ECP!"312v").

In that case, if that cell in column AC contains that value (for example cell AC5100), I want to add the text "Valid" in cell D5100 and the text "Correct" in cell E5100.
So for every cell in column AC where that value ("ECP") is contained, I want to add the text "Valid" and "Correct" in that same row in columns D and E.

Could someone please help me with that macro code should be. Much appreciated!

 

[My Aswer Is This]

You want "Valid" in D And "Correct" in E is that correct?

 

[Lowbob131]

Yes that is correct. If the condition is met in AC Column, we would add the text “Valid” in column D and “Correct” in column E in that same row the condition is met.

I have a long spreadsheet with 10,000 lines where I need to run that macro line by line to see if that condition is met

Please let me know if anything is unclear

Thank you very much!

 

[My Aswer Is This]

Try this:

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

 

[Lowbob131]

Try this:

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

This looks to be working, thanks a lot !

 

[My Aswer Is This]

This looks to be working, thanks a lot !

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[Lowbob131]

Another quick question - How can I add 2 criteria to this code?
For example if that first condition we have AND if there a “C” in column AD, then we would have that result.

So same formula as above, but just adding one criteria to it (And “C” in column AD, then …)

Thank you

 

[Lowbob131]

Hello,
A few additional questions if you are able to help me:
1) How do we add multiple text criteria to look up specific text in various columns (like the one we currently have above but look up a specific text in a few more columns and where all the criteria are met simultaneously, then we would have the same result)

I tried the below code but it doesn't seem to work. I just want to add a few additional criteria with an AND function so that if all the criteria are met then the macro would add the below values in columns D and E.

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") _
And InStr(Cells(i, "AD"), "ZXS") _
And InStr(Cells(i, "AE"), "FGW") Then
Cells(i, "D").Value = "Valid"
Cells(i, "E").Value = "Correct"
End If

Next
Application.ScreenUpdating = True
End Sub


2) I would like to assign the macros in buttons that I have created in another tab in that excel. Is it possible to assign macros in these buttons that are located in another tab of that excel?
I tried doing that but the macro doesn't seem to work. It only works if I am on the same tab with the long dataset and go on the Developer tab menu and run the Macro directly.

Do I need to specify in the code the tab the Macro needs to run on?


3) Is it possible to assign multiple macros to the same button so that all macros run together and I can sort this long dataset?

The end product I am trying to accomplish is have a bunch of macros running different criteria, filling the text in other columns like the example above. If I have 50+ different macros like the one above, can I link them to a button and run all the macros simultaneously? Or do I need to write a single code with OR functions in between to do that?

I would like to automate that spreadsheet as much as possible

Thanks for your help!

 

[My Aswer Is This]

Well I wrote a script that works like below but yes if you want to have a button on sheet named "Bravo" But want to run the script on sheet named Alpha you need to tell me and the script the sheet names.
And to need 50 different scripts to perform a task seems like a awful lot and you have never mentioned this before.
And sure we can write a script to run all 50 scripts script when just pressing one button.
But if there is any problem with one script the whole thing will cause a error and it could be hard to tell what script caused the error.

I like helping here but if user keeps adding more to his wish list one at a time it can cause problems.
So the above script works for me.

I added the sheet name as a example:

Sub Check_Column_Ac()
'Modified  2/5/2022  9:38:00 PM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
'Note below: Here we use the sheet named  "Alpha"  Modify this to what you want The script runs on this sheet no matter what sheet the button is on.
'Note you will see a . before each cells  must be .cells since we are using with
Lastrow = Sheets("Alpha").Cells(Rows.Count, "AC").End(xlUp).Row

    For i = 1 To Lastrow
        With Sheets("Alpha")
            If InStr(.Cells(i, "AC"), "ECP") And InStr(.Cells(i, "AD"), "ZXS") And InStr(.Cells(i, "AE"), "FGW") Then
                .Cells(i, "D").Value = "Valid"
                .Cells(i, "E").Value = "Correct"
            End If
        End With
    Next
Application.ScreenUpdating = True
End Sub