0

Suppose there are two Worksheet 1, Worksheet 2. Both the Worksheets are have a 'Number' Column which ties the records together Workshhet 2 has 'Date' Column for each unique Number in the 'Number' column, Whenever Worksheet 2 'Date' column is changed i want to update a Column 'Times Changed' in Worksheet 1 for the unique Number in Worksheet 1 associated with the unique Number of Worksheet 2. Please help me guys! :)

I have tried the following 

Private Sub Worksheet_Change(ByVal Target As Range) 
If Target.Address = "$A$1" Then [C5].Value = [C5].Value + 1 
End Sub
Community
  • 1
  • 1
user3331363
  • 305
  • 1
  • 4
  • 13
  • Please show us what have you tried? If you haven't tried anything then start with `worksheet_change` event. [THIS](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) will get you started :) – Siddharth Rout Feb 20 '14 at 17:21
  • BTW Welcome to stackoverflow :) Since you are new here, take some time out and see [Stack Overflow question checklist](http://meta.stackexchange.com/questions/156810/stack-overflow-question-checklist) Questions asking for code must demonstrate a minimal understanding of the problem being solved.Include attempted solutions, why they didn't work, and the expected results. – Siddharth Rout Feb 20 '14 at 17:22
  • Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then [C5].Value = [C5].Value + 1 End Sub Then tried using Vlookup to reference this data to the first worksheet. However I want to know if there is better way of doing this!! :) – user3331363 Feb 20 '14 at 17:28
  • I would recommend reading the link in my second comment and then editing your question to make it more meaningful else your question will end up getting closed. Also Whatever relevant code that you tried, put that in your question and not in the comments. :) – Siddharth Rout Feb 20 '14 at 17:31
  • The code you have showed is working for me, are you sure that you have Events Enabled? – user2140261 Feb 20 '14 at 17:32
  • Thanks!! I'll make sure I read the checklist. The Code works for me too. I was wondering how to change the Target address to refer to the entire column A not just a particular cell. So when a cell in column A has been changed it should populate the count in the appropriate cell in column c. – user3331363 Feb 20 '14 at 17:42

1 Answers1

1

Your code should work within the same sheet. If you want to count the number of times the cell is changed in an other sheet, say Sheet2, then you would have to change the code to something like:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then
        ActiveWorkbook.Sheets("Sheet2").Cells(Target.Row, 3).Value = ActiveWorkbook.Sheets("Sheet2").Cells(Target.Row, 3).Value + 1
    End If
End Sub

This change event only reacts on change to cells in A1:A100 and then changes the count in the corresponding cell in the C row on Sheet2.

Netloh
  • 4,338
  • 4
  • 25
  • 38
  • Thanks! If the Target address is a entire column A and any cell update will then populate the appropriate cell in column C. For e.g. I update A100, and the update count needs to be populated in C100. How would the code change :) – user3331363 Feb 20 '14 at 17:52
  • I have updated the code in my answer to take this into account. – Netloh Feb 20 '14 at 18:03
  • This works great! I have a follow up question. Incase column A is tied to unique app no (column B) in the current worksheet(Sheet 1). Sheet 2 also has unique app no in say column B. First I need to check if both the app no matches and then update column C in sheet 2. Is this possible? – user3331363 Feb 20 '14 at 18:27
  • I am not entirely sure about what you question is. I would recommend that you create a new question instead. – Netloh Feb 20 '14 at 19:40
  • I was able to take care of the second part of my question by doing a vlookup. It works like a treat. Thanks for your help Soren!! – user3331363 Feb 22 '14 at 00:01
  • Good to hear. Please accept the answer if it worked out for you. :) – Netloh Feb 22 '14 at 12:01
  • Thanks for letting me know. Just did that :) – user3331363 Feb 27 '14 at 21:17