slang
08-30-2013, 07:37 AM
Hello, been a while.
I am a bit stumped on this and forgive me if it is as simple as a syntax error :)
I have a sheet that fills an array formula down a column of data but the size of the data can change weekly. I need a way to reference the last row in the R1C1 formula so it doesnt play havoc with the array formula. Here is what I got JUST EXPERIMENTIG WITH THE FIRST REFERENCE TO SEE IF IT WILL TAKE IT BUT IT WILL NOT.
Sub junk()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("u2").Select
Selection.FormulaArray = _
"=IF(RC[-1]>=LARGE(IF(R2C16:R" & lastrow & "c16=RC[-5],R2C20:R2973C20,0),ROUND(COUNTIF(R2C16:R2973C16,RC[-5])/5,0)),""A""," & Chr(10) & "IF(RC[-1]<=SMALL(IF(R2C16:R2973C16=RC[-5],R2C20:R2973C20,99^99),ROUND(COUNTIF(R2C16:R2973C16,RC[-5])/2,0)),""C"",""B""))"
Range("u2").Select
Selection.AutoFill Destination:=Range("u2:u" & lastrow), Type:=xlFillDefault
End Sub
Am I way off base here with the approach or do I have my syntax wrong?:aw
TGIF
I am a bit stumped on this and forgive me if it is as simple as a syntax error :)
I have a sheet that fills an array formula down a column of data but the size of the data can change weekly. I need a way to reference the last row in the R1C1 formula so it doesnt play havoc with the array formula. Here is what I got JUST EXPERIMENTIG WITH THE FIRST REFERENCE TO SEE IF IT WILL TAKE IT BUT IT WILL NOT.
Sub junk()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("u2").Select
Selection.FormulaArray = _
"=IF(RC[-1]>=LARGE(IF(R2C16:R" & lastrow & "c16=RC[-5],R2C20:R2973C20,0),ROUND(COUNTIF(R2C16:R2973C16,RC[-5])/5,0)),""A""," & Chr(10) & "IF(RC[-1]<=SMALL(IF(R2C16:R2973C16=RC[-5],R2C20:R2973C20,99^99),ROUND(COUNTIF(R2C16:R2973C16,RC[-5])/2,0)),""C"",""B""))"
Range("u2").Select
Selection.AutoFill Destination:=Range("u2:u" & lastrow), Type:=xlFillDefault
End Sub
Am I way off base here with the approach or do I have my syntax wrong?:aw
TGIF