Max of a range help

[Matt S]

I am trying to define the last row of column A as a constant, which I am able
to do. Then I want to apply that constant to the MAX and MIN functions, as
so:

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("AM4").Select
ActiveCell.FormulaR1C1 = "=MIN(R[7]C:R[LastRow]C)"

But this gives me an error stating Run-Time error '1004':
Application-defined or object-defined error.

I've had success using the LastRow value in the autofill (from other hints
on these boards) as so:

Selection.AutoFill Destination:=Range("BI11:BI" & LastRow)

 

[Bob Phillips]

ActiveCell.FormulaR1C1 = "=MIN(R[7]C:R[" & LastRow & "]C)"


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Maybe

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("AM4").Select
ActiveCell.FormulaR1C1 = "=MIN(R[7]C:R[" & LastRow & "]C)"

Mike

 

[Matt S]

Thank you Bob! I was just talking to a colleague and he mentioned that I
would not be able to use the LastRow constant within the brackets, so I tried
exactly what you have typed there and it worked out... then of course I came
here and saw that you and Mike had already posted it.

Thanks for the quick reply!
<3 Matt

ActiveCell.FormulaR1C1 = "=MIN(R[7]C:R[" & LastRow & "]C)"


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

I am trying to define the last row of column A as a constant, which I am
able
to do. Then I want to apply that constant to the MAX and MIN functions,
as
so:

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("AM4").Select
ActiveCell.FormulaR1C1 = "=MIN(R[7]C:R[LastRow]C)"

But this gives me an error stating Run-Time error '1004':
Application-defined or object-defined error.

I've had success using the LastRow value in the autofill (from other hints
on these boards) as so:

Selection.AutoFill Destination:=Range("BI11:BI" & LastRow)

 

[Rick Rothstein \(MVP - VB\)]

Paired quote marks delineate text constants... so, any text located between
two paired quote marks is considered text. You have the word LastRow between
paired quote marks, so VB see them as the letters L, a, s, t, R, o and w,
and not as the name of your variable. If you think about it, this is a good
thing. For example, what if you had a variable name "a" in your code, would
you want VB substituting its assigned value every there was the letter "a"
in a text string assignment? For example,

a = "Hello"
MsgBox "What a great view!"

Would you really want VB to interpret all those "a"'s as "Hello" so that the
MessageBox displayed this...

WhHellot Hello greHellot view!"

To make your assignment see LastRow as a variable, you have to concatenate
it to the other text...

ActiveCell.FormulaR1C1 = "=MIN(R[7]C:R[" & LastRow & "]C)"

Rick