Solved

Excel edit dups in source

Posted on 2012-04-10
13
248 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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
 

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

829 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