# Using a Variable Argument in VBA Formulas (Functions) • ### 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

• 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 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

End Sub```

• Proposed as answer by 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 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