Solved

Accumulate data at cell

Posted on 2010-11-15
5
694 Views
Last Modified: 2012-05-10
Hi Experts,

I would like to request Experts help to add additional feature in the attached script. Need to copy and accumulate date value from Column_C at Send sheet to Status sheet at Column B and Column_C at Return sheet to Status sheet at Column C and update column D with the latest location sheet of the number. I have attached the sample data at Status sheet for Experts to get better view. Hope Experts will help me to add this feature.



Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Target.Offset(1, 0).Select
    End If
    If Target.Row < 3 Then Exit Sub
    If Not Application.Intersect(Target, Columns(1)) Is Nothing Then
        Target.Offset(, 1) = Environ("username")
        Target.Offset(, 2) = Now()
      
        With Worksheets("Status")
            Set r = .Columns(1).Find(Target.Offset(, 0), LookIn:=xlValues)
            If r Is Nothing Then
                rn = .UsedRange.Rows.Count + 1
                .Cells(rn - 1, 1).Copy .Cells(rn, 1)
                .Cells(rn, 1).Value = Target.Offset(, 0)
            End If
        End With
    End If
End Sub

Open in new window

DataCopy.xls
0
Comment
Question by:Theva
  • 3
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
Peter Kwan earned 500 total points
ID: 34142554
Please try. But one point to note, if you add two rows of the same CNumber in the same day (e.g. 16-Nov), then there will be "16-Nov;16-Nov" in the same cell.
DataCopy.xls
0
 

Author Comment

by:Theva
ID: 34142905
Hi,

Thanks for the code. I noticed at Status sheet when we update the C-number its always copying the row 2 cell format (color and font type) how to prevent this?  
0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 34142923
Just remove the two lines:

.Cells(rn - 1, 1).Copy .Cells(rn, 1)
.Cells(rn - 1, 3).Copy .Cells(rn, 3)
0
 

Author Closing Comment

by:Theva
ID: 34143201
Hi,

Thanks a lot for the superb solution
0
 

Author Comment

by:Theva
ID: 34163592
Hi,

Hope you will consider this request. Intent to integrate new feature  with your solution.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26623691.html
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

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 42
Help with Excel formula 6 38
MS Excel IF AND OR statement 3 31
Dynamic Filter ? 4 22
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

863 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

23 Experts available now in Live!

Get 1:1 Help Now