1. It's assumed that the problem is to determine the range of values
to be calculated, calculate and write them back to a worksheet in a
different range.
2. In worksheet LogData the source data starts at cell B2, the
results at C2.
3.1 Get rngSource, passing it to the calculation sub.
3.2 Put rngSource into arrLog, calculating the LOG of the source
data.
3.3 Pass arrLog back to the calling function, assigning arrLog to
rngResults.
3.4 The results will automatically appear in column C.
'''''''''''''''''''''''''''''''''''
Public Sub getSourceData()
Dim rngSource As Range, ctSource As Integer, rngColB As Range
Dim rngResults As Range, arrCalcResults As Variant
Set rngColB = Worksheets("LogData").Range("$B:$B")
ctSource = Application.WorksheetFunction.Count(rngColB)
With Worksheets("LogData")
Set rngSource = .Range("$B$2:$B$" & ctSource + 1)
Set rngResults = .Range("$C$2:$C$" & ctSource + 1)
End With
arrCalcResults = calcLogOfRange(rngSource)
rngResults = arrCalcResults
Set rngSource = Nothing: Set rngResults = Nothing
End Sub
Public function calcLogOfRange(rngSource As Range) As Variant
Dim arrLog As Variant, i As Integer
arrLog = rngSource
For i = 1 To UBound(arrLog)
arrLog(i, 1) = Application.WorksheetFunction.Log(arrLog(i, 1))
Next
calcLogOfRange = arrLog
Set rngSource = Nothing
End Function