asked on

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

Rayne

ASKER

This also is related from previous solution:
https://www.experts-exchange.com/questions/27668583/Data-to-Edit-Excel.html

Saqib Husain

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

Rayne

ASKER

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

Rayne

ASKER

attached file...

Rayne

ASKER

any ideas?

Saqib Husain

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

Rayne

ASKER

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

Saqib Husain

I do not see the unique column in this file. Which one is it?

Rayne

ASKER

It is the column with name = "PK"

Rayne

ASKER

in the attached file

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

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

Start Free Trial

Rayne

ASKER

Now thats what I call awesome. Thank you Sir :)
You saved my life, greatly acknowledged

-R

Rayne

ASKER

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