Make Your Microsoft Dynamics Investment Count & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.
Sub LogRatio()
Dim cel As Range, rg As Range
Dim i As Long, j As Long, jStart As Long, k As Long, nRows As Long, nCols As Long
With ActiveSheet
Set rg = .UsedRange
Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1) 'First row contains header labels
nCols = rg.Columns.Count
jStart = .Range("G2").Column - (rg.Column - 1)
For j = jStart To nCols Step 13
On Error Resume Next
For k = 1 To 2
Do
Set cel = Nothing
Set cel = rg.Columns(j + k - 1).Find(0, LookAt:=xlWhole, LookIn:=xlValues)
If cel Is Nothing Then Exit Do
cel.Value = 1
cel.Offset(0, 3 - k).FormulaR1C1 = "=LN(RC[-1]/RC[-2])/LN(2)" 'Use this statement if formula only in rows where 0 changed to 1
Loop
Next
'rg.Columns(j).Offset(0, 2).FormulaR1C1 = "=LN(RC[-1]/RC[-2])/LN(2)" 'Use this statement if formula needed in every row
Next
End With
End Sub
jStart = .Range("G2").Column - (rg.Column - 1)
For j = jStart To nCols Step 13
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.