Solved

Excel edit dups in source

Posted on 2012-04-10
13
244 Views
Last Modified: 2012-04-17
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
Comment
Question by:Rayne
  • 9
  • 4
13 Comments
 

Author Comment

by:Rayne
ID: 37829021
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37829305
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
 

Author Comment

by:Rayne
ID: 37829677
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
 

Author Comment

by:Rayne
ID: 37829686
attached file...
0
 

Author Comment

by:Rayne
ID: 37830433
any ideas?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37830614
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Rayne
ID: 37835413
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37835544
I do not see the unique column in this file. Which one is it?
0
 

Author Comment

by:Rayne
ID: 37835773
It is the column with name = "PK"
0
 

Author Comment

by:Rayne
ID: 37835777
in the attached file
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 37835908
Sheets("Source").Cells(Sheets("Source").Range("I:I").Find(Cells(sr, "I"), , xlValues).Row, Target.Column) = Target.Value
0
 

Author Comment

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

-R
0
 

Author Comment

by:Rayne
ID: 37858451
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now