View Full Version : Dynamic Formula VBA
Shazam
02-26-2008, 12:59 PM
Hi everyone,
I would like this formula below to have a dynamic range. So change this
Range("G1").Select
Selection.FormulaArray = _
"=INDEX(R2C3:R162C3,SMALL(IF(R2C1:R162C1=""1"",IF(R2C2:R162C2=RC[-1],ROW(R2C2:R162C2)-ROW(R2C2)+1)),COUNTIF(R1C6:RC[-1],RC[-1])))"
Into this...
"=INDEX(Range("C2:C" & Cells(Rows.Count, "a").End(xlUp).Row),SMALL(IF(Range("A2:A" & Cells(Rows.Count, "a").End(xlUp).Row)=""1"",IF(Range("B2:B" & Cells(Rows.Count, "a").End(xlUp).Row)=RC[-1],ROW(Range("B2:B" & Cells(Rows.Count, "a").End(xlUp).Row))-ROW(R2C2)+1)),COUNTIF(R1C6:RC[-1],RC[-1])))"R2C2:R162C2>TRANSPOSE(Data!R2C2:R162C2))+0,ROW(Data!R2C2:R162C2)^0))"
See I incorporated this code
Range("C2:C" & Cells(Rows.Count, "a").End(xlUp).Row)
into the formula but it doesn't work.
Any way to get this to work?
mdmackillop
02-26-2008, 01:34 PM
Can you post the formula which should appear in the cell for some arbitrary range.
BTW I would tackle this by setting one dynamic range, and using rng.offset(,2).address etc. into your formula
Tinbendr
02-26-2008, 01:41 PM
Change
Range("C2:C" & Cells(Rows.Count, "a").End(xlUp).Row
to
Range("C2:C" & Range("C65536").End(xlUp).Rows.Count)
Norie
02-26-2008, 01:49 PM
shazam
That syntax is just wrong.
To see what I mean open the immediate window (CTRL+G) and enter this.
?"=INDEX(Range(""C2:C"" & Cells(Rows.Count, "a").End(xlUp).Row)"
What's the result?
=INDEX(Range("C2:C" & Cells(Rows.Count, ).End(xlUp).Row)
That doesn't mean anything on a worksheet.
PS I realise it's not the whole formula, just trying to illustrate the point.:)
Bob Phillips
02-26-2008, 02:54 PM
Dim mpRange1 As String
Dim mpRange2 As String
Dim mpRange3 As String
mpRange1 = "R2C1:R" & Cells(Rows.Count, "A").End(xlUp).Row & "C1"
mpRange2 = "R2C3:R" & Cells(Rows.Count, "A").End(xlUp).Row & "C2"
mpRange3 = "R2C3:R" & Cells(Rows.Count, "A").End(xlUp).Row & "C3"
Range("G1").FormulaArray = _
"=INDEX(" & mpRange3 & ",SMALL(IF(" & mpRange1 & "=""1"",IF(" & _
mpRange2 & "=RC[-1],ROW(" & mpRange2 & ")-ROW(R2C2)+1)),COUNTIF(R1C6:RC[-1],RC[-1])))"
Shazam
02-26-2008, 03:02 PM
Thanks for the replying everyone.
Can you post the formula which should appear in the cell for some arbitrary range.
BTW I would tackle this by setting one dynamic range, and using rng.offset(,2).address etc. into your formula
Hi mdmackillop,
Here is the formula I'm currenly using.
=INDEX($C$2:$C$162,SMALL(IF($A$2:$A$162="1",IF($B$2:$B$162=F1,ROW($B$2:$B$162)-ROW($B$2)+1)),COUNTIF($F$1:F1,F1)))
Hi Tinbendr,
I tried your code but I'm getting a compile error.
That doesn't mean anything on a worksheet.
PS I realise it's not the whole formula, just trying to illustrate the point.:)
Hi Norie,
Your right I never used the CTRL+ G before. I'm going to read up on it.
Shazam
02-26-2008, 03:08 PM
Dim mpRange1 As String
Dim mpRange2 As String
Dim mpRange3 As String
mpRange1 = "R2C1:R" & Cells(Rows.Count, "A").End(xlUp).Row & "C1"
mpRange2 = "R2C3:R" & Cells(Rows.Count, "A").End(xlUp).Row & "C2"
mpRange3 = "R2C3:R" & Cells(Rows.Count, "A").End(xlUp).Row & "C3"
Range("G1").FormulaArray = _
"=INDEX(" & mpRange3 & ",SMALL(IF(" & mpRange1 & "=""1"",IF(" & _
mpRange2 & "=RC[-1],ROW(" & mpRange2 & ")-ROW(R2C2)+1)),COUNTIF(R1C6:RC[-1],RC[-1])))"
Once again Thanks xld it work very well.
Thanks You!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.