Log Function In A Macro

    Hello,
    I have a lots of number arranged in a column. I want to take log of each number (on the base 2) and show the result in the adjacent column. I want this to be in a macro and the results to be displayed all at a time (I dont want to drag the cursor down to get log values for number corresponding to each row).

    Re: Log Function In A Macro


    Hi


    I have no idea why you would want to do this via a macro but perhaps:


    Code
    Sub LogarithmGen()
Selection.Offset(, 1).Value = Evaluate("IF(ROW(" & Selection.Address & "),LN(" & Selection.Address & "))")
End Sub


    Select the range of values eg A2:A1000 and run the macro. It will place the results in column B. It avoids looping and is thus very fast.


    Richard

    Re: Log Function In A Macro


    Thanks for the reply.
    But this will not help me.


    I am actually making a bigger macro which includes taking LOG of the values. As i am still a begginer with VB can you help me with exact code for taking input from A2:A200 and output at B2:B200

    Re: Log Function In A Macro


    OK, so more of a learning thing then? Best to use a loop:


    Code
    Sub Log2()
Dim rData As Range, rCell As Range
Set rData = Sheets("Sheet1").Range("A2:A200")  'set our data range that we want to log
For Each rCell In rData   'use a collection of individual cells to loop thru our range
    If IsNumeric(rCell.Value) And rCell > 0 Then 'check we have a valid value
        rCell.Offset(0, 1).Value = Log(rCell.Value) 'use offset to place result in column B
    End If
Next rCell 'go to the next cell
End Sub


    You may want to change the sheet name (eg if it isn't Sheet1).


    Hope this helps!


    Richard

    Re: Log Function In A Macro


    Sorry, I made an incorrect assumption that you meant the natural log. Amend the code to:


    Code
    Sub Log2()
Dim rData As Range, rCell As Range
Set rData = Sheets("Sheet3").Range("A2:A200")  'set our data range that we want to log
For Each rCell In rData   'use a collection of individual cells to loop thru our range
    If IsNumeric(rCell.Value) And rCell > 0 Then 'check we have a valid value
        rCell.Offset(0, 1).Value = Log(rCell.Value) / Log(2#) 'use offset to place result in column B, divide natural log by log(base) to get correct result.
    End If
Next rCell 'go to the next cell
End Sub


    Richard

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login