PDA

View Full Version : Solved: VBA loop through columns with relative referencing



Tdude
12-16-2012, 07:11 AM
Hello everybody, I am a brand-new member and most importantly a very fresh coder dealing with my first nested loops.
the aim of my macro is to calculate the ln return of some data in different columns.
For this reason I need to loop through the rows of a single column, apply the Ln formula and then move to the next column.
since I use relative referencing offsetting + and - columns I cannot get the right way to run my simple macro correctly.

first of all lets assume I have some data in columns 1 to 3 and need to apply my formula and display the results in column 4 to 6.
therefore my active cell, or better my starting point is cell D1.
The first loop through rows of a single column should be like this:


Sub Lnreturns()
Range("D1").Select
Do While ActiveCell.Offset(1, -3) <> ""
ActiveCell.Offset(1, 0).Value = WorksheetFunction.Ln _
(ActiveCell.Offset(1, -3) / ActiveCell.Offset(0, -3))
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Now
the next step should be to insert a For/Next loop in order to change column.
like:
For c=1 to 3
Do While ActiveCell.Offset(1, -c) <> ""
'the rest of the code....
next c
the thing is that since I am adding and subtracting column to my active cell
I cannot find the right way to use this loop
Apologies for the lack of elegance in my code.
Ty for your help

p45cal
12-16-2012, 07:44 AM
You can use the likes of:
Cells(1,4).Select
instead of:
Range("D1").Select
[Cells uses Cells(RowNo,ColumnNo)]
So for the sake of the exercise your outer loop can be
For c = 4 to 6
leaving:Sub Lnreturns()
For c = 4 To 6
Cells(1, c).Select
Do While ActiveCell.Offset(1, -3) <> ""
ActiveCell.Offset(1, 0).Value = WorksheetFunction.Ln(ActiveCell.Offset(1, -3) / ActiveCell.Offset(0, -3))
ActiveCell.Offset(1, 0).Select
Loop
Next c
End Sub

Tdude
12-16-2012, 08:07 AM
That's exactly what I was looking for!
Thanks a lot p45cal for the quick and clever answer.