asked on

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!!!!

Subodh Tiwari (Neeraj)

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

Abigail Evans

ASKER

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

Subodh Tiwari (Neeraj)

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?

Abigail Evans

ASKER

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

Subodh Tiwari (Neeraj)

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?

Abigail Evans

ASKER

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.

Subodh Tiwari (Neeraj)

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

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Abigail Evans

ASKER

It worked! Thank you so much for your help! I really appreciate that!

Subodh Tiwari (Neeraj)

You're welcome Abigail! Glad it worked as desired.

Abigail Evans

ASKER

Thank you for your help! You are greatly appreciated!