Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max of a range help
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max of a range help
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" <Matt wrote in message ... 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max of a range help
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" wrote: 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max of a range help
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" wrote: 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" <Matt wrote in message ... 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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max of a range help
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 "Matt S" <Matt wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming | |||
how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming |