multiple VBA

[jojowiththaflow]

Is there a way to get this code to apply to multiple cells - say C2 through C9?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInterest As Range
Set rInterest = Range("H2")
Dim rTotal As Range
Set rTotal = Range("C2")
Dim rList As Range
Set rList = Range("J1")
Dim lRow As Long
' ignore multiple changes, eg, deleting cell contents
If Target.Cells.Count > 1 Then Exit Sub
'Only monitor the cell of interest
If Intersect(Target, rInterest) Is Nothing Then Exit Sub
' only look at numbers
If Not IsNumeric(rInterest.Value) Then Exit Sub
Application.EnableEvents = False
rList.Resize(, 2).Value = Array("Data", "Time Stamp")
lRow = Cells(Rows.Count, rList.Column).End(xlUp).Row + 1
Cells(lRow, rList.Column).Value = rInterest.Value
Cells(lRow, rList.Column + 1).Value = Now
rTotal.Value = rTotal.Value + rInterest.Value
rInterest.Value = ""
rInterest.Select
Application.EnableEvents = True

End Sub

 

[Joe4]

Welcome to the Board!

Please explain in more detail exactly what your data looks like and what you would like to happen.
It isn't quite clear, as you are asking to apply it to C2:C9, though your code appears to be looking for changes in column H, not column C, to determine when to run.

 

[jojowiththaflow]

So in cells C2 through C9 I want to be able to put in numbers and have the corresponding cells in H keep a running total

 

[Joe4]

Since I really do not understand what your code is doing (it is hard to visual it when I don't know what your data looks like), the best I can do is guess.
Anyway, the top of this code will show you how to trigger the code to run when a value in C2:C9 is updated.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rInterest As Range
    Dim rTotal As Range
    Dim rList As Range
    Dim tRow As Long
    Dim lRow As Long
    
'   ignore multiple changes, eg, deleting cell contents
    If Target.Cells.Count > 1 Then Exit Sub
    
'   Check to see if updated value is range C2:C9
    If Intersect(Target, Range("C2:C9")) Is Nothing Then Exit Sub
    
'   Get row of cells that was updated
    tRow = Target.Row

'   Set ranges, according to the row that was updated
    Set rInterest = Cells(tRow, "H")
    Set rTotal = Target
    Set rList = Cells(tRow, "J")
    
'   only look at numbers
    If Not IsNumeric(rInterest.Value) Then Exit Sub
    
    Application.EnableEvents = False
    rList.Resize(, 2).Value = Array("Data", "Time Stamp")
    lRow = Cells(Rows.Count, rList.Column).End(xlUp).Row + 1
    Cells(lRow, rList.Column).Value = rInterest.Value
    Cells(lRow, rList.Column + 1).Value = Now
    rTotal.Value = rTotal.Value + rInterest.Value
    rInterest.Value = ""
    rInterest.Select
    Application.EnableEvents = True

End Sub

 

[jojowiththaflow]

Is there a way I can send you my Excel file to help you see the data?

 

[Joe4]

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Some people will also upload the files to public sharing sites and provide links. Note that many people cannot or will not download the files due to security concerns (especially if VBA is attached).
I do not have the ability to download files from my current location (my workplace forbids it), but may be able to from home.

In any event, please also include a detailed description of how this should all work along with your data. Maybe walk us through a simple example.

 

[jojowiththaflow]

here's the link to my spreadsheet (https://drive.google.com/file/d/1aNayQND4PtRUWp4OIttDe3zvL2yuzWxp/view?usp=sharing)

So the current code I have in the takes the numbers I enter into H2 and keeps a running total in C2. I like to achieve the same thing with all the heighted yellow cells being input cells and the corresponding highlighted green cells being the running totals. The code that is in there now also does a time stamp of each entry in columns K & L. That is not something that I necessarily need.

 

[Joe4]

So, it sounds to me like you really want this code to run when column H is updated (and have it update column C with the running total and column K with the date/time stamp).

That code would simply look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim tRow As Long
    
'   ignore multiple changes, eg, deleting cell contents
    If Target.Cells.Count > 1 Then Exit Sub
    
'   Check to see if updated value is range H2:H9
    If Intersect(Target, Range("H2:H9")) Is Nothing Then Exit Sub
    
'   Get row of cells that was updated
    tRow = Target.Row
    
'   Update value in column C to add value from column H to existing valu
    Cells(tRow, "C") = Cells(tRow, "C") + Target
    
'   Add date/timestamp to column K
    Cells(tRow, "K") = Now()
    
End Sub

 

[jojowiththaflow]

I feel we are getting very close. Now, can we add to it so that when columns I & J are updated, columns D & E (respectively) update with the running totals?

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim tRow As Long
    Dim tCol As Long
    
'   ignore multiple changes, eg, deleting cell contents
    If Target.Cells.Count > 1 Then Exit Sub
    
'   Check to see if updated value is range H2:J9
    If Intersect(Target, Range("H2:J9")) Is Nothing Then Exit Sub
    
'   Get row and columns of cell that was updated
    tRow = Target.Row
    tCol = Target.Column
    
'   Update value in column 5 columns to the left of updated cell
    Cells(tRow, tCol - 5) = Cells(tRow, tCol - 5) + Target
    
'   Add date/timestamp to column K
    Cells(tRow, "K") = Now()
    
End Sub

A tip on how to get the most out of this board. When asking questions like this, it is often best to lay it all out there at once, rather than ask bit-by-bit, as if the person responding does not know the full scope of what you are trying to do, then may initially give you answers that are too specific, and are not going to work for your "larger" problem than you are trying to solve.

As you can see, I had to change the logic for how we are updating the columns (can no longer hard-code to column "C"). In this case, it wasn't a big deal, but it can sometimes turn into a big deal (especially if someone took the time to write some extensive code, only to find out that it won't work based on the "real" conditions). I have seen people get their feather ruffled on occasion (feeling like they wasted a bunch of time on a solution the person cannot use).