VBA Express : Excel - VBA loop through columns with relative referencing

Excel

VBA loop through columns with relative referencing

Ease of Use

Easy

Version tested with

2003

Submitted by:

Tdude

Description:

I need to create a nested loop in order to apply the Ln formula to a bunch of rows and columns.

Discussion:

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. Can anybody kindly help me? It would be very very much appreciated. Thank you in advance

Code:

instructions for use

			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

How to use:

Test the code:

Sample File:

No Attachment

Approved by Jacob Hilderbrand

This entry has been viewed 29 times.