Adding with VBA

[figuare9]

I'm looking for a way to add whatever number I type in my active cell to the cell immediately adjacent to it to the right.

However, the part that gets tricky.. Is that I only want this to work in a range of cells. . . not the entire sheet.


I have a tally of numbers in the range N5:N24, R5:R24, V5:V24, Z5:Z24 & below them, I have another set of ranges with a different tally of numbers in ranges N33:N52, R33:R52, V33:V52, Z33:Z52.

I'd like to be able to enter a number in the column before the numbers that will automatically add it to the numbers to the right when I hit enter on the keyboard, but only have it function when my active cell is in ranges M5:M24, M33:M52, R5:R24, R33:R52, V5:V24, V33:V52, Z5:Z24, Z33:Z52

Is something like that possible?

so if I have 72 in N21 and I enter in 8 in M21 and hit enter, I'd like N21 to = 80.

 

[mikerickson]

Adjust the code for KeyRange to suit your situation

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keyRange As Range, changeRange As Range
    Dim oneCell As Range
    Set keyRange = Range("M5:M24,M33:M52"): Rem adjust
    Set changeRange = Application.Intersect(Target, keyRange)
    If Not changeRange Is Nothing Then
        Application.EnableEvents = False
        For Each oneCell In changeRange
            With oneCell
                .Offset(0, 1).Value = Val(CStr(.Offset(0, 1).Value)) + Val(CStr(.Value))
            End With
        Next oneCell
    End If
    Application.EnableEvents = True
End Sub

 

[figuare9]

Thanks for the reply! It's mostly working. However, i have some issues that are kind of affecting the way this code works.

So I have a button that clears out any data I've been working with. In this case;

Private Sub ClearData1_Click()
Range("N5:N24").ClearContents
Range("M5:M24").ClearContents
End Sub

I also have a formula in the column to the right of the number tallys that were mentioned in the OP.

So, e.g. Column M I want to use to add to column N. And in O I have a formula.

When I clear the contents of N & M, this code is leaving a zero in column N, and removing the formula I have in Column O. .

Does that make sense? HA! I'll post a picture of the table if needed. It's kind of hard to explain..

Thank you btw!

Basically,

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD="align: center"]Wage[/TD]
[TD="align: center"](#s to add per your formula)[/TD]
[TD="align: center"]Total Hours[/TD]
[TD="align: center"]Total Wage [=SUM(L5*N5)][/TD]
[/TR]
[TR]
[TD="align: center"]$20.00[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$200[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

Column N should not be in your expansison of KeyRange.
I can't see anything that should clear the column O cells.

 

[figuare9]

...Yea that was my fault. I had copy pasted the range wrong. It was causing weird issues with the " " " in the wrong places. All fixed!

Thank you very much for the assistance. It's working exactly as intended. Great help! You're amazing!