Adding with VBA

figuare9

Board Regular
Joined
Oct 23, 2017
Messages
118
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Adjust the code for KeyRange to suit your situation
Code:
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
 
Last edited:
Upvote 0
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;

Code:
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,

LMNO
Wage(#s to add per your formula)Total HoursTotal Wage [=SUM(L5*N5)]
$20.00510$200

<tbody>
</tbody>
 
Last edited:
Upvote 0
Column N should not be in your expansison of KeyRange.
I can't see anything that should clear the column O cells.
 
Upvote 0
...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!
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top