Excel edit dups in source

Hello All,

I have attached the file. I need help with vba.
Now if I enter data in the colored cells and refresh, the change gets effected in the source worksheet. The attached macro works great if the item is unique to the source list. But actually there will be repetitions of item numbers in the source list. In that case, what could be done accurately update the source sheet.
For example –
There are two identical items – i1 and i1 on the unfiltered list. For example I select P1 and P2 ion the check-boxes, and put 77 and 88 on the column F corresponding to the items i1 and i1 and then I refresh, I don’t see the changes  - 77 and 88 when I again unclick and click the check-boxes.
The likely hood of items repeating in the source list will happen and the same item can be used under different programs
So its not updating correctly in that case...what can be done to correct that update if items appear more than once in the source list?


Thank you
R
FilteredData2Edit-Dups.xlsm
RayneAsked:
Who is Participating?
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.

RayneAuthor Commented:
0
Saqib Husain, SyedEngineerCommented:
Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sr As Long, rw As Range, cel As Range, nf As Boolean
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 5 And Target.Column <> 6 Then Exit Sub
    sr = Target.Row
    For Each rw In Sheets("Source").Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
        nf = False
        For Each cel In rw.Resize(1, 9).Cells
            If cel.Column <> Target.Column Then
                If Cells(sr, cel.Column) <> cel Then nf = True: Exit For
            End If
        Next cel
        If Not nf Then Sheets("Source").Cells(rw.Row, Target.Column) = Cells(sr, Target.Column): Exit Sub
    Next rw
End Sub

Open in new window

0
RayneAuthor Commented:
Hello Ssaqibh

Thanks for your reply. One issue was that when I added new items to the source table, it would not work properly. Its working if I change the items  numbers within the current source table.

I added some new rows in the source table and now when I select M2, the new rows don’t get accounted for. If you check M2 that pulls up the new added row. Now when add 3 and 4 to the [hold off] column of the [i2]s and refresh, only the first i2 gets updated, Not the second one that just got added to the table. So I think it’s the range issue – how to modify that with a growing and new rows added to the table?
FilteredData2Edit-newrows.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RayneAuthor Commented:
attached file...
0
RayneAuthor Commented:
any ideas?
0
Saqib Husain, SyedEngineerCommented:
Now try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sr As Long, rw As Range, cel As Range, nf As Boolean
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 5 And Target.Column <> 6 Then Exit Sub
    sr = Target.Row
    For Each rw In Sheets("Source").Range("C1:C" & Sheets("Source").Range("C" & Rows.Count).End(xlUp).Row)
        nf = False
        For Each cel In rw.Resize(1, 9).Cells
            If cel.Column <> Target.Column Then
                If Cells(sr, cel.Column) <> cel Then nf = True: Exit For
            End If
        Next cel
        If Not nf Then Sheets("Source").Cells(rw.Row, Target.Column) = Cells(sr, Target.Column): Exit Sub
    Next rw
End Sub

Open in new window

0
RayneAuthor Commented:
I have changed the code and used you your previous one – the code that you supplied recently doesn’t solves the problem (it cat correctly identify the correct row to change) so I decided to include a unique row number that will help in the process (PK). Can you please modify your code to now look at the unique row number and make the needed changes when user inputs their data? Since there is a unique row number, even if the items repeat, doesn’t matter as the excel is looking at the unique row column and not at the repeating item column. Can you assist?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sr As Long
If Target.Count > 1 Then Exit Sub
If Target.Column <> 5 And Target.Column <> 6 Then Exit Sub
sr = Target.Row
Sheets("Source").Cells(Sheets("Source").Range("I:I").Find(Cells(sr, "I")).Row, Target.Column) = Target.Value
End Sub



R
Copy-of-FilteredData2Edit-Dups.xlsm
0
Saqib Husain, SyedEngineerCommented:
I do not see the unique column in this file. Which one is it?
0
RayneAuthor Commented:
It is the column with name = "PK"
0
RayneAuthor Commented:
in the attached file
0
Saqib Husain, SyedEngineerCommented:
Sheets("Source").Cells(Sheets("Source").Range("I:I").Find(Cells(sr, "I"), , xlValues).Row, Target.Column) = Target.Value
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
RayneAuthor Commented:
Now thats what I call awesome. Thank you Sir :)
You saved my life, greatly acknowledged

-R
0
RayneAuthor Commented:
Hello Again,

On the main sheet – when I deleted a column, your code is not working. I am posting a follow up question to this please reply.

Thank you
0
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
Microsoft Office

From novice to tech pro — start learning today.

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.