I have this two cell accumulator code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub
which is well know and found at http://www.mcgimpsey.com/excel/accumulator.html , I'd like to expand it to two whole columns, let's say columns G and F
I tried it but can't make it work, can you guys make it happen?
The above code takes the number entered in A1 and adds it to the value in B1 - so the first time you input 2 in A1, a1 will show 2, then you input 10 in a1 and b1 shows 12
Do you want to paste data into columns G & F and add the total of these columns to B1?
I'd like to apply that code for whole columns insted of just to two cells:
values added in, let's say, G1 get added in the next cell F1, values added in G2 get added in F2 and so on
Hi Aija,
To do what you want just duplicate the IF statement in the sub for each cell you want to test, and change the cell references accordingly e.g.
- change A1 to G1 and B1 to F1
But you may end up with a lot of code.
Without knowing exactly what it is you are trying to achieve that's the best answer I can give. As Purfleet said, perhaps just adding the total of the values in each column is a simpler solution? You do say that you want to do this for whole columns.
It's better to describe the problem and expected outcome than say how you want the solution to look. By telling us that we must use VBA and add values as described, it limits our ability to offer alternative solutions as we don't actually understand the problem you are trying to solve.
Regards
Phil
