Using Min() in VBA

[GarysStudent]

If I want the minimum of the values from A1 thru A10, I can enter:

=Min(A1:A10) in a cell.

In VBA I can use:


Sub Sample()
Dim r As Range, v As Variant
Set r = Range("A1:A10")
v = Application.WorksheetFunction.Min(r)
End Sub




Now if I want the minimum value of A10 across sheets, I can enter:

=MIN(Sheet1:Sheet10!A10) in a worksheet cell.

I am trying to do the same thing in VBA using
Application.WorksheetFunction.Min

I can't find the correct syntax. The best I can find is:


v = Evaluate("Min(Sheet1:Sheet10!A10)")

Is this my only option??

[Bob Phillips]

You could use


v = Application.Min(Worksheets("Sheet1").Range("A10"), Worksheets("Sheet2").Range("A10"), _
Worksheets("Sheet3").Range("A10"), Worksheets("Sheet4").Range("A10"), _
Worksheets("Sheet5").Range("A10"), Worksheets("Sheet6").Range("A10"), _
Worksheets("Sheet7").Range("A10"), Worksheets("Sheet8").Range("A10"), _
Worksheets("Sheet9").Range("A10"), Worksheets("Sheet10").Range("A10"))

or


v = Application.Min([Sheet1!A10], [Sheet2!A10], [Sheet3!A10], [Sheet4!A10], [Sheet5!A10], _
[Sheet6!A10], [Sheet7!A10], [Sheet8!A10], [Sheet8!A10], [Sheet10!A10])

but the only you showed looks best to me.

[GarysStudent]

Thank you!

[snb]

only written differently:


Sub M_snb()
x3 = [min(sheet1:sheet4!B2)]
End Sub