Converting a formula into code

[Navvy]

I have the following formula and I am trying to convert it into code using a do...while loop.

The formula is:
=IF(ISNUMBER(M1),MIN(ABS(LN(M1/N1)),ABS(LN(L1/N1))),ABS(LN(L1/N1)))*R1

I need this to calculate for all cells until the last empty cell is found and put the value in column D. I want this as code and not a formula.


Thanking you!

 

[SARUN]

Navvy,

Try this

Sub Trial()
Dim mCtr As Integer
Dim mResult As Integer
mCtr = 1
Do
If Range(&quot;M&quot; & mCtr).Value <> &quot;&quot; Then
If VBA.IsNumeric(Range(&quot;M&quot; & mCtr).Value) Then
Range(&quot;D&quot; & mCtr).Value = Min(Abs(Ln(Range(&quot;M&quot; & mCtr).Value / Range(&quot;N&quot; & mCtr).Value)), Abs(Ln(Range(&quot;L&quot; & mCtr).Value / Range(&quot;N&quot; & mCtr).Value)))
Else
Range(&quot;D&quot; & mCtr).Value = Abs(Ln(Range(&quot;L&quot; & mCtr).Value / Range(&quot;N&quot; & mCtr).Value)) * Range(&quot;R&quot; & mCtr).Value
End If
Else
Exit Do
End If
While True
End Sub



&quot;Whereever you go there are people who need you for what you can do...&quot;

 

[TonyJollans]

SARUN,

I'm afraid your solution has a couple of failings.

[ul][li]Do ... While True is not a valid VBA construct[/li][li]mCtr needs incrementing somewhere and should be a Long (not an Integer) to cater for more than 32K possible rows[/li][li]The calculation does not replicate the original where the result of the IF was always multiplied by R1[/li][li]Unless it has changed post-97, neither MIN nor LN are VBA functions - you need to use WorksheetFunction.Min and Log instead[/li][/ul]At the risk of setting myself up for someone else to point out my failings I offer the following amended version which should work better.

Sub Trial()
    Dim mCtr As Long
    For mCtr = 1 To 65536
        If Range(&quot;M&quot; & mCtr).Value <> &quot;&quot; Then
            If IsNumeric(Range(&quot;M&quot; & mCtr).Value) Then
                Range(&quot;D&quot; & mCtr).Value = WorksheetFunction.Min(Abs(Log(Range(&quot;M&quot; & mCtr).Value / Range(&quot;N&quot; & mCtr).Value)), Abs(Log(Range(&quot;L&quot; & mCtr).Value / Range(&quot;N&quot; & mCtr).Value))) * Range(&quot;R&quot; & mCtr).Value
            Else
                Range(&quot;D&quot; & mCtr).Value = Abs(Log(Range(&quot;L&quot; & mCtr).Value / Range(&quot;N&quot; & mCtr).Value)) * Range(&quot;R&quot; & mCtr).Value
            End If
        Else
            Exit For
        End If
    Next
End Sub

Enjoy,
Tony

 

[Zathras]

This may be a little easier for a TechnicalUser (or a mathematician) to follow and modify if necessary:

Option Explicit

Private Function AbsLogXoverY(X As Double, Y As Double) As Double
  If Y = 0 Then
    AbsLogXoverY = 0
  Else
    AbsLogXoverY = Abs(Log(X / Y))
  End If
End Function

Sub DoCalcs()
Dim rWork As Range
Dim c As Range
Dim MString As String
Dim L As Double
Dim M As Double
Dim N As Double
Dim R As Double
Dim ABSLN_L As Double
Dim ABSLN_M As Double
Dim Partial As Double

  Set rWork = Intersect(ActiveSheet.UsedRange, Range("D1:D65536"))
  For Each c In rWork
    L = c.Offset(0, 8).Value
    MString = c.Offset(0, 9).Value
    N = c.Offset(0, 10).Value
    R = c.Offset(0, 14).Value
    ABSLN_L = AbsLogXoverY(L, N)
    If (MString <> &quot;&quot;) And (VBA.IsNumeric(MString)) Then
      M = MString
      ABSLN_M = AbsLogXoverY(M, N)
      If ABSLN_L < ABSLN_M Then
        Partial = ABSLN_L
      Else
        Partial = ABSLN_M
      End If
    Else
      Partial = ABSLN_L
    End If
    c.Value = Partial * R
  Next c
  Set rWork = Nothing
End Sub

We have all assumed that the R factor is on each line. If not, remove the line where it says R=c.Offset.... and insert this line between the Set... and the For...

  R = rWork.Cells(1, 15).Value

 

[SARUN]

Tony,

Thanks pointing out the mistakes.

&quot;Do ... While True is not a valid VBA construct&quot;, if you can pl. direct me on the cons of using Do While.

&quot;mCtr needs incrementing somewhere ....&quot;

How many times I've told myself to INCREMENT the counter. (I did bang my head on the table when you pointed it)

And I'm using Office XP and still exploring...

Arun


&quot;Whereever you go there are people who need you for what you can do...&quot;

 

[TonyJollans]

Arun,

Sorry if my post seemed over-critical. There is nothing wrong with Do While - I use it plenty, it's just the syntax that's wrong you need ...

Do While True

' Do your stuff here

[/code]
If [/code]

condition

 Then
        Exit Do
    EndIf
Loop

In effect the &quot;While True&quot; is a default and all you actually need in the first line above is &quot;Do&quot; all by itself.

I'm still using 97 and I think I'll spend my life exploring.

Enjoy,
Tony

 

[SARUN]

Tony,

No Problem, I get it. Hope Navvy has seen your comments too.

Good to keep learning.

Thanks

&quot;Whereever you go there are people who need you for what you can do...&quot;

 

[xlbo]

Hmmm - -personally, I find loops for entering stuff like this take faaaaaar to long - make use of excel's native capabilities:

Sub EnterFormula()
lRow = cells(65536,13).end(xlup).row
Range(&quot;D2&quot; & lRow).formula = &quot;=IF(ISNUMBER(M1),MIN(ABS(LN(M1/N1)),ABS(LN(L1/N1))),ABS(LN(L1/N1)))*R1&quot; ' assumes header in D1
range(&quot;D2&quot; & lRow).copy
range(&quot;D2&quot; & lRow).pastespecial paste:=xlvalues
end sub


Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde