PDA

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!