• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 39
  • Last Modified:

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!!!!
0
Abigail Evans
Asked:
Abigail Evans
  • 6
  • 5
1 Solution
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now