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).
Log Function In A Macro



Re: Log Function In A Macro
Hi
I have no idea why you would want to do this via a macro but perhaps:
CodeSub 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:
CodeSub 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
Thanks, It is working fine. Just one more thing... I want on the log base 2


Re: Log Function In A Macro
Sorry, I made an incorrect assumption that you meant the natural log. Amend the code to:
CodeSub 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

Re: Log Function In A Macro
Thanks a lot!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!