Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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
0
Rayne
Asked:
Rayne
  • 9
  • 4
1 Solution
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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
 
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
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 your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 9
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now