PDA

View Full Version : [SOLVED] How to jump next to column



tzien25
02-03-2014, 04:25 AM
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
02-03-2014, 04:50 AM
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
02-03-2014, 04:59 AM
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
02-03-2014, 05:08 AM
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
02-03-2014, 05:13 AM
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
02-03-2014, 05:20 AM
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
02-03-2014, 05:31 AM
it's work now. tks for helping

GTO
02-03-2014, 06:04 AM
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
02-03-2014, 06:39 AM
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
02-03-2014, 07:40 AM
You're welcome. Thanks for the feedback.

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