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
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