locked
Using a Variable Argument in VBA Formulas (Functions) RRS feed

  • Question

  • Hello All,

    Lim = Worksheets("Sheet1").Cells(4, 2) + 1
         For Count = 2 To Lim
                ActiveSheet.Cells(Count, 4).Formula = "=norminv(rand(),B1,B2)"
                ActiveSheet.Cells(2, 5).Formula = "=max(D2,D100000)"
            Next Count

    The above excerpt is part of my Macro and the bold underlined part is the line I need help with.  I need the argument of the max formula to be from cell D2: D(Lim) (instead of from D2:D100000) where Lim is just a number being input defined in the first line here.  I hope there is an easy solution.  All suggestions and ideas are welcome.  Thank You!

    NOTE:  If you are experienced with VBA and you are certain (or relatively certain) that this cannot be done please let me know so I do not keep wasting my time.  What I have works it is just inefficient and slow because of this bug.
    Friday, January 15, 2010 6:33 PM

Answers

  • I'm pretty new to VBA, but from my understanding, this would work:

    Lim = Worksheets("Sheet1").Cells(4, 2) + 1
         For Count = 2 To Lim
                ActiveSheet.Cells(Count, 4).Formula = "=norminv(rand(),B1,B2)"
                ActiveSheet.Cells(2, 5).Formula = "=max(D2,D" & CStr(lim) & ")"
            Next Count
    Since it is just a string you are putting in, you can just concatenate the value right in, and when it is interpreted as a formula, the actual value will be there giving you the "variable" effect you want.

    I hope that helped.

    Daniel
    • Marked as answer by BillMurph Tuesday, January 19, 2010 3:10 PM
    Monday, January 18, 2010 3:17 PM

All replies

  • Do I understand correctly that D100,000 Should really be a the last cell in a range?
     if so i would find the last cell of your range. In any case I mocked something up.  I hope this helps
    Sub count_rows()
    Dim myCalculated_Range As Range
    end_cell = Range(Cells(4, 4), Cells(4, 4).End(xlDown)).Rows.Count
    Set myCalculated_Range = Range(Cells(4, 4), Cells(end_cell, 4))
    
    For Each c In myCalculated_Range
        c.Value = Application.WorksheetFunction.NormInv(rand(), B1, B2)
    Next c
    
    answer = Application.WorksheetFunction.Max(myCalculated_Range)
    End Sub

    • Proposed as answer by ilivni Saturday, January 16, 2010 2:00 AM
    Saturday, January 16, 2010 2:00 AM
  • I'm pretty new to VBA, but from my understanding, this would work:

    Lim = Worksheets("Sheet1").Cells(4, 2) + 1
         For Count = 2 To Lim
                ActiveSheet.Cells(Count, 4).Formula = "=norminv(rand(),B1,B2)"
                ActiveSheet.Cells(2, 5).Formula = "=max(D2,D" & CStr(lim) & ")"
            Next Count
    Since it is just a string you are putting in, you can just concatenate the value right in, and when it is interpreted as a formula, the actual value will be there giving you the "variable" effect you want.

    I hope that helped.

    Daniel
    • Marked as answer by BillMurph Tuesday, January 19, 2010 3:10 PM
    Monday, January 18, 2010 3:17 PM
  • oh, if you wanted a range for cells to pull out the max value from, replace the comma between D2 and D"&Cstr(lim)&")" with a colon, though I'm sure you would have figured that out.

    Daniel
    Monday, January 18, 2010 3:21 PM
  • Daniel,

    This worked perfectly!  You were right here the max function uses a comma and not a colon.

    Thank You,
    Bill

    Tuesday, January 19, 2010 3:12 PM