MIN Date Value of Range

[sbrotz]

Looking for insight - appreciate the glance.

I am attempting to determine the minimum date within a dynamic range using the following:

Range("D1").Select
Selection.FormulaArray = _
"=MIN(DATA!R[1]C:R[" & NumRows - 1 & "]C)"

The information resides in the DATA worksheet, column D, starting at D2 and ending at the number of rows that are counted by the function "NumRows"
The expectation is that the result of the formula =MIN(D2:D137) be placed into the specifified cell on the starting worksheet. Upon running the macro - I error out.

What am I doing wrong?

 

[wigi]

Can you please post the full code - including that one for NumRows?

Also, use

 tags to make sure code readable. Thanks.

 

[sbrotz]

I don't know how to insert code tags, but have included the requested information below

NumRows = ActiveSheet.UsedRange.Rows.Count


Range("D1").Select
Selection.FormulaArray = _
"=MIN(DATA!R[1]C:R[" & NumRows - 1 & "]C)"

Appreciate the view......

 

[wigi]

To add code tags, you should type in your post:

then paste your code, and lastly, type:

['/code]

WITHOUT the ' in front of the /

Thanks for the consideration.

 

[wigi]

Try:

Range("D1").Formula = "=MIN(R[1]C:R[" & NumRows - 1 & "]C)"

You are sure that you know about dynamic ranges in Excel?
http://www.ozgrid.com/Excel/DynamicRanges.htm

 

[sbrotz]

Wigi - that did the trick. Appears I was trying to make it more complicated than what needed to be.

Thank you!