Max of a range help

M

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)
 
B

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)
 
M

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
 
M

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

Bob Phillips said:
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)



Matt S said:
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)
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Autofill Dynamic Range of Cells 2
Range With Variables 1
Macro will copy formula instead of value 6
Error on Autofill 2
Row spec 4
Defining destination Range 5
Copy to row xx 5
Autofill Error 4

Top