BradleyS
05-07-2012, 08:35 AM
Hi
I have a couple of formulas as shown below in my macro code:
-------------------------------
Range("Q3").Select
Selection.FormulaArray = _
"=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX(general!R2C6:R507C6,MATC H(1,IF(general!R2C1:R507C1=RC1,IF(ISNUMBER(SE ARCH(R1C[-1],general!R2C13:R507C13)),1))))))"
Range("R3").Select
Selection.FormulaArray = _
"=LOOKUP(REPT(""z"",255),CHOOSE({1,2},"""",INDEX(general!R2C14:R507C14,MATCH(1,IF(general!R2C1:R507C1=RC1,IF(ISNUMBER (SEARCH(R1C[-1],general!R2C13:R507C13)),1))))))"
-------------------------------
...however the range of rows in the general worksheet will keep changing and I wondered how I can alter this formula so that it includes the correct amount of rows regardless of row length, so that when I run my macro it includes everything?
I have a couple of formulas as shown below in my macro code:
-------------------------------
Range("Q3").Select
Selection.FormulaArray = _
"=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX(general!R2C6:R507C6,MATC H(1,IF(general!R2C1:R507C1=RC1,IF(ISNUMBER(SE ARCH(R1C[-1],general!R2C13:R507C13)),1))))))"
Range("R3").Select
Selection.FormulaArray = _
"=LOOKUP(REPT(""z"",255),CHOOSE({1,2},"""",INDEX(general!R2C14:R507C14,MATCH(1,IF(general!R2C1:R507C1=RC1,IF(ISNUMBER (SEARCH(R1C[-1],general!R2C13:R507C13)),1))))))"
-------------------------------
...however the range of rows in the general worksheet will keep changing and I wondered how I can alter this formula so that it includes the correct amount of rows regardless of row length, so that when I run my macro it includes everything?