CODEWITHSUNDEEP
excelvba
user4356954
user4356954

Reputation:

Calculate logarithm for whole column excel

let us suppose we have following image : enter image description here

i want to calculate logarithm for A column, i wrote following code which will calculate log of each A value and write inside A column, here is my code :

Private Sub Log_transform_Click()
Dim rng As Range

Set rng = Range("A2:A41")

For Each cell In rng
cell.Value = Log(cell.Value)
Next cell



End Sub

my question is : how to write result in B column?thanks in advance

Upvotes: 0

Views: 161

Answers (4)

Derby9421
Derby9421

Reputation: 231

Below output Log(A_column); depends on the context in case you meant natural logarithm (ln) instead, replace Cells(i, 2).Value = Log(Cells(i, 1).Value) as Cells(i, 2).Value = WorksheetFunction.Ln(Cells(i, 1).Value).

Sub CalcLog()
    Dim lastRow As Long
    Dim i As Integer
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lastRow
        If Cells(i, 1).Value > 0 Then
            Cells(i, 2).Value = Log(Cells(i, 1).Value)
        Else
            MsgBox "error: value < 0"
        End If
    Next i
End Sub

e.g. outputs,

enter image description here

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

In Excel VBA, in general, it takes much longer to access worksheet cells to manipulate them than it does to do the work in a VBA array. (five times as long in many of my measurements).

If you are going to do the calculations in VBA, I would recommend reading the range into an array (one step); doing the calculations, then writing the results back (also in a single step).

The coding is more complex, but execution time is much faster.

For example, for your problem, assuming data starts in A1 of a worksheet:

Option Explicit
Sub logs()
    Dim rSrc As Range
    Dim v As Variant, I As Long
    
With ThisWorkbook.Worksheets("Sheet6") 'or whatever worksheet
    Set rSrc = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    v = rSrc 'v will be a 2D array
End With
    
For I = 2 To UBound(v, 1) 'start at 2 to avoid column header
    v(I, 1) = Log(v(I, 1)) / Log(10#)
Next I
v(1, 1) = "Log(Price)"

Application.ScreenUpdating = False
With rSrc.Offset(columnoffset:=1)
    .EntireColumn.Clear
    .Value = v
    .EntireColumn.AutoFit
End With

End Sub

Of course, if you use a Table, you can simply enter a formula in the first cell of the Log(Price) column and the formula will fill down automagically:

=LOG([@Price])

Note that VBA Log function returns the natural log, whereas the Excel worksheet function LOG returns the base-10 log. Hence the VBA formula is a bit different to return the same result.

Upvotes: 1

taller
taller

Reputation: 18778

  • One option without looping
  • Set formula first and then convert to values
Option Explicit

Sub Demo()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    With Range("B2:B" & LastRow)
        ' Set formula
        .FormulaR1C1 = "=LOG(RC[-1])"
        ' Covert formula to value, if you need
        .Value = .Value
    End With
End Sub

Upvotes: 0

user4356954
user4356954

Reputation:

I got following solution -the main idea was that like a python or matlab, that ar vectorised programming languages, if i can apply the same principle here, if i will do it mannualy with for loop, solution is that :

Private Sub Log_transform_Click()
Dim rng As Range
Dim n   As Integer


Set rng = Range("A2:A41")
n = WorksheetFunction.Count(rng)
For i = 2 To n + 1
Cells(i, 2).Value = Log(Cells(i, 1))


Next i



End Sub

Upvotes: 0

Related Questions