If a cell matches another cell, select multiple rows and change them to x

I have two sheets. On sheet 1, cell L4 is a value that can be changed by user input. This value is the ID. On sheet 2 is an array of data for everything associated to that ID (name, date, location etc).
What I am looking for is a macro so that Exel takes the ID code entered in sheet 1 cell l4, then looks for it in column I on sheet 2. Once each row with the ID is located, it. Should fill these rows with the letter x. Instead of the old data.


Can anyone help? All input is appreciated!!!!
Abigail EvansWorkforce Capacity Plan AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Place the following code on Sheet1 Module.
To do so, right click on Sheet1 Tab --> View Code --> Paste the code given below into the opened code window --> Close the VB Editor --> Save your workbook as Macro-Enabled Workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim dws As Worksheet
Dim dlr As Long, dlc As Long
If Target.Address(0, 0) = "L4" Then
    Application.EnableEvents = False
    Set dws = Sheets("Sheet2")
    dlr = dws.UsedRange.Rows.Count
    dlc = dws.UsedRange.Columns.Count
    dws.AutoFilterMode = False
    With dws.Range("A1").CurrentRegion
        'Assuming the IDs are in column I on Sheet2, starting from Row2 where Row1 being the header Row
        .AutoFilter field:=9, Criteria1:=Target.Value
        If dws.Range("I1:I" & dlr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
            dws.Range(dws.Cells(2, "A"), dws.Cells(dlr, dlc)).SpecialCells(xlCellTypeVisible).Value = "X"
            dws.Range("I2:I" & dlr).SpecialCells(xlCellTypeVisible).Value = Target.Value
        End If
        .AutoFilter
    End With
    Application.EnableEvents = True
End If
End Sub

Open in new window

0
Abigail EvansWorkforce Capacity Plan AnalystAuthor Commented:
I am having challenges with the code. It says variable not found. I have attached the workbook so you can see a visual. Sheet 1 is the Form sheet (J4) and sheet 2 is the raw data sheet (column I)
Workflow-Timeline-with-macros2.20.2.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I am not sure where did you place the suggested code.
Anyways, can you describe your requirement once again based on the sample workbook you provided?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Abigail EvansWorkforce Capacity Plan AnalystAuthor Commented:
Greetings,


 What I am looking for is a macro so that Exel takes the ID code entered in the "form" sheet cell L4, then looks for it in column I on the raw data sheet.
 I need all rows in in the "raw data sheet" that contain the ID code in "L4" on the "form" sheet to be replaced with the value "x".


 I hope this clarifies things.  I have uploaded a different workbook in there. Thank you very much for helping!
Time-line-for-expert-review.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Do you want the code to run automatically when you input an ID in L4 on Form Sheet? Or you want a button which needs to be clicked to run the macro after you input the ID in L4?

BTW the ID "BoiShe" in L4 at present is not found in column I on Raw Data Sheet. In that case macro is not supposed to do anything. Right?
0
Abigail EvansWorkforce Capacity Plan AnalystAuthor Commented:
Thank you for you quick response.

I want it attached to the "edit" button on the form page.

Since "Boishe" is not present in the Raw Data Sheet the macro would not do anything.

I apologize, I put the "Boishe" data in the form and did not hit the "update" button to run the macro pasting the data onto the Raw Data Sheet give you something to work with.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, replace the existing code on Module2 with the following code and then on Form Sheet, right click on the button Edit --> choose Assign Macro and choose the macro "UpdateRawDataBasedOnID" from the macro list and click OK.

Code for Module2:
Sub UpdateRawDataBasedOnID()
Dim sws As Worksheet, dws As Worksheet
Dim dlr As Long
Dim ID As String

Application.ScreenUpdating = False

Set sws = Sheets("Form")
Set dws = Sheets("Raw Data Sheet")
dlr = dws.Cells(Rows.Count, 1).End(xlUp).Row
ID = sws.Range("L4").Value
dws.AutoFilterMode = False
With dws.Range("A1").CurrentRegion
    'Assuming the IDs are in column I on Sheet2, starting from Row2 where Row1 being the header Row
    .AutoFilter field:=9, Criteria1:=ID
    If dws.Range("I1:I" & dlr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        dws.Range("A2:I" & dlr).SpecialCells(xlCellTypeVisible).Value = "X"
        dws.Range("I2:I" & dlr).SpecialCells(xlCellTypeVisible).Value = ID
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Open in new window

1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please refer to the attached with the suggested code on Module2 and it is assigned to the Edit button on Form Sheet.
Time-line-for-expert-review.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Abigail EvansWorkforce Capacity Plan AnalystAuthor Commented:
It worked! Thank you so much for your help! I really appreciate that!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Abigail! Glad it worked as desired.
0
Abigail EvansWorkforce Capacity Plan AnalystAuthor Commented:
Thank you for your help! You are greatly appreciated!
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.