How to jump next to column

[tzien25]

Hello
I'm new with vba and I try to code some vba here's my example


1 A B C D
2 3 5 1 2
3 1 2 0 1
4 1 3 0 1
5 1 0 0 0
A2= Sum(A3:A5)
B2= Sum(B3:B5)
etc

I want to sum in line 2 all values below but i dont know how to switch to next collum in vba

[erestel]

Assuming I am understanding you correctly, you want to start at A6

Range("A6").Select

If not, find out the column you are at:

Dim i, col As Integer
col = ActiveCell.Column

Then use a Loop to move from one column to the next.


For i = 1 To 4
Cells(6, col).Select


'Do stuff


col = col + 1
Next i

Voila! =]

[tzien25]

tks for quick reply, i means i want sum in line 2 :) will try your sol and feedback asap :)

tried but error 1004 :( can u help me with this

[erestel]

If you are calculating the sum for one row only, then you can do away with the Loop.

If you mean moving one row down, use ActiveCell.Row instead of ActiveCell.Column

[tzien25]

1 A B C D
2 3 5 1 2
3 1 2 0 1
4 1 3 0 1
5 1 0 0 0
A2= Sum(A3:A5)
B2= Sum(B3:B5)
etc


edit for clearly my problems :(.

[lecxe]

Hi tzien
Welcome to the board

I would need more information to be comfortable with a solution. I've made some assumptions, hope it helps.

Assumptions:
- data in contiguous rows in columns A to D
- data start in row 3
- all columns with the same number of rows
- empty row after the table
- at least 2 rows with data

This code gets you in each cell in row 2 the sum of the cells below.

Hope this is what you need



Sub Test()

With Range("A2:D2")
.Formula = "=SUM(A3:A" & Range("A3").End(xlDown).Row & ")"
.Value = .Value
End With
End Sub


Remark: in this code I first write the Sum() formulas and then convert them to values.
If you want to keep the formula in the cell delete the .Value statement

[tzien25]

it's work now. tks for helping

[GTO]

For others... Cross-posted at: http://www.mrexcel.com/forum/excel-questions/754888-how-jump-next-column.html

tzien25:

I am off to bed, so do not have time to look up a particularly nice explanation of proper bumping and cross-posting. I hope someone will post you the link. Whilst certainly not a teen, I totally understand wanting to conquer a problem and hope you maintain your voraciousness for doing so. That said, cross-posting prematurely and without the politeness of notifying those reading either/many threads wastes the 'answerers' time; which, is donated. I hope that makes sense.

Mark

[tzien25]

For others... Cross-posted at:

tzien25:

I am off to bed, so do not have time to look up a particularly nice explanation of proper bumping and cross-posting. I hope someone will post you the link. Whilst certainly not a teen, I totally understand wanting to conquer a problem and hope you maintain your voraciousness for doing so. That said, cross-posting prematurely and without the politeness of notifying those reading either/many threads wastes the 'answerers' time; which, is donated. I hope that makes sense.

Mark
sr about this :(.

BTW lexie can u show me to use your code in another sheet :( It's just work on the sheet I code dont work on another sheet
silly me, it's working now. :(

[lecxe]

You're welcome. Thanks for the feedback.

Comment: make sure you read Mark's post about the cross-posting.