Some quick Excel help (codes and stuff)

Ubik

Hey I just had a question for anyone who can help me out real quick. I'm trying to add all of column A starting at A20 and continuing on to the end of A. The thing is, I don't want to set an end cell because I'll keep adding more things to the A column

I found this code online, which works well:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("A20:" & Range("A" & _
ActiveSheet.Rows.Count). End(xlUp).Address)
[A1] = Application.Sum(rng)
End Sub

This adds everything as I input it and displays it in Cell A1.

Now my problem is, how can I duplicate this to also add B20:the rest of B and put it in A2, C20:the rest of C and put it in A3, etc. etc.

I tried:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim rng2 As Range

Set rng = Range("A20:" & Range("A" & _
ActiveSheet.Rows.Count). End(xlUp).Address)
Set rng2 = Range("B20:" & Range("B" & _
ActiveSheet.Rows.Count). End(xlUp).Address)

[A1] = Application.Sum(rng)
[A2] = Application.Sum(rng2)
End Sub

But this just freezes excel. I probably have some logic error or infinite loop or something but I'm not familiar enough with Excel to know what I'm doing. Also, searching the web hasn't helped much for my particular case.

Any help would be greatly appreciated.

Jeice

What kind of error are you getting? I ran almost the exact same code as you and it works for me. The only difference is, I didn't put the code in a Worksheet_Change method. Here's my code:

Sub macro1() // this line is the only difference

Dim rng As Range
Dim rng2 As Range

Set rng = Range("A20:" & Range("A" & _
ActiveSheet.Rows.Count).End(xlUp).Address)
Set rng2 = Range("B20:" & Range("B" & _
ActiveSheet.Rows.Count).End(xlUp).Address)

[A1] = Application.Sum(rng)
[A2] = Application.Sum(rng2)

End Sub

Note, I only did this code where there are values in column a and b.

foggrat

Try:

=SUM(A:A) - SUM(A1:A19)
=SUM(B:B) - SUM(B1:B19)
etc.

This should do what you need.

Ubik

Thanks for both replies, I'll try those out when I get a chance.

I'm not getting an error so much as Excel just freezes up and then stops responding. It's Excel like 2002 if that matters.

BlochWave

(now with 100% more edit AGAIN)
That code puts it in an infinite loop because you put it in a worksheet change function.

Lesson time:

1) Press escape to break code execution and go into debug mode when that happens. Set breakpoints (click in the silver space to the left of the line of code) and hit play to see if it's going in circles. It is

2)I think it's because you change something in the worksheet, which triggers code that then changes something in the worksheet, which triggers code that then changes something in the worksheet which triggers code that changes something in the worksheet.......

>>>>But then I'd expect it to happen in your first case too, which it doesn't. I'll poke around<<< POKED

You can update the value of a cell like you do in the first case, but as soon as you go and do something else that counts as a change I guess, and starts the loop. Take your code, private sub worksheet_change(byval target as range), and in the body just put [A1] = 5, and change a value in the sheet. Awesome. Now add [A2] = 7. Fail

So do the non VBA solution the other guy said, or why not just have cell A1 = sum(A20:A65536) or whatever the last cell in Excel 03 is? All the extra 0s won't hurt, and A2=sum(B20:B65536)