Adding with VBA

figuare9

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

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,956
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:

figuare9

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

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,956
Column N should not be in your expansison of KeyRange.
I can't see anything that should clear the column O cells.
 

figuare9

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

Forum statistics

Threads
1,089,270
Messages
5,407,306
Members
403,132
Latest member
Black_Mamba_1666

This Week's Hot Topics

Top