|
Writing Array formulas in code
Hi, I'm trying to record/write an array formula in VBA with an ISERROR formula on the front. I've tried recording and writing it with no success. When recording I got an Unable to Record error and when writing I got a unable to set the FormulaArray property of the range class error. The formula i have is FormulaArray = _ "=INDEX('Dispatch Summary'!R[-4]C:R[4995]C[78],SMALL(IF('Dispatch Summary'!R[-4]C:R[4995]C[78]=R1C2,ROW('Dispatch Summary'!R[-4]C:R[4995]C[78])),ROW(R[-4])),2)" The formula i need is =IF(ISERROR(INDEX('Dispatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)),"zzz",INDEX('Di spatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)) Is it possible to do? Thanks for any help. Simon |
Writing Array formulas in code
Hi It seems that you need a cell reference: Range("A1").FormulaArray=..... Hopes this helps -- Per "Simon - M&M" skrev i meddelelsen ... Hi, I'm trying to record/write an array formula in VBA with an ISERROR formula on the front. I've tried recording and writing it with no success. When recording I got an Unable to Record error and when writing I got a unable to set the FormulaArray property of the range class error. The formula i have is FormulaArray = _ "=INDEX('Dispatch Summary'!R[-4]C:R[4995]C[78],SMALL(IF('Dispatch Summary'!R[-4]C:R[4995]C[78]=R1C2,ROW('Dispatch Summary'!R[-4]C:R[4995]C[78])),ROW(R[-4])),2)" The formula i need is =IF(ISERROR(INDEX('Dispatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)),"zzz",INDEX('Di spatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)) Is it possible to do? Thanks for any help. Simon |
Writing Array formulas in code
i created a named range MyData for 'Dispatch Summary'!A1:CA5000 then Range("D8").FormulaArray = "=IF(ISERROR(INDEX(mydata,SMALL(IF(mydata=$B$1,ROW (mydata)),ROW(1:1)),2)),""zzz"",INDEX(mydata,SMALL (IF(mydata=$B$1,ROW(mydata)),ROW(1:1)),2))" worked fine "Per Jessen" wrote in message ... Hi It seems that you need a cell reference: Range("A1").FormulaArray=..... Hopes this helps -- Per "Simon - M&M" skrev i meddelelsen ... Hi, I'm trying to record/write an array formula in VBA with an ISERROR formula on the front. I've tried recording and writing it with no success. When recording I got an Unable to Record error and when writing I got a unable to set the FormulaArray property of the range class error. The formula i have is FormulaArray = _ "=INDEX('Dispatch Summary'!R[-4]C:R[4995]C[78],SMALL(IF('Dispatch Summary'!R[-4]C:R[4995]C[78]=R1C2,ROW('Dispatch Summary'!R[-4]C:R[4995]C[78])),ROW(R[-4])),2)" The formula i need is =IF(ISERROR(INDEX('Dispatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)),"zzz",INDEX('Di spatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)) Is it possible to do? Thanks for any help. Simon |
Writing Array formulas in code
Results = Evaluate("ISERROR(INDEX('Dispatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)") if Results = true then Results = "zzz" else Results = Evaluate("INDEX('Dispatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)") end if =IF(ISERROR(INDEX('Dispatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)),"zzz",INDEX('Di spatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)) "Simon - M&M" wrote: Hi, I'm trying to record/write an array formula in VBA with an ISERROR formula on the front. I've tried recording and writing it with no success. When recording I got an Unable to Record error and when writing I got a unable to set the FormulaArray property of the range class error. The formula i have is FormulaArray = _ "=INDEX('Dispatch Summary'!R[-4]C:R[4995]C[78],SMALL(IF('Dispatch Summary'!R[-4]C:R[4995]C[78]=R1C2,ROW('Dispatch Summary'!R[-4]C:R[4995]C[78])),ROW(R[-4])),2)" The formula i need is =IF(ISERROR(INDEX('Dispatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)),"zzz",INDEX('Di spatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)) Is it possible to do? Thanks for any help. Simon |
Writing Array formulas in code
Works a treat, Thank you very much. "Patrick Molloy" wrote: i created a named range MyData for 'Dispatch Summary'!A1:CA5000 then Range("D8").FormulaArray = "=IF(ISERROR(INDEX(mydata,SMALL(IF(mydata=$B$1,ROW (mydata)),ROW(1:1)),2)),""zzz"",INDEX(mydata,SMALL (IF(mydata=$B$1,ROW(mydata)),ROW(1:1)),2))" worked fine "Per Jessen" wrote in message ... Hi It seems that you need a cell reference: Range("A1").FormulaArray=..... Hopes this helps -- Per "Simon - M&M" skrev i meddelelsen ... Hi, I'm trying to record/write an array formula in VBA with an ISERROR formula on the front. I've tried recording and writing it with no success. When recording I got an Unable to Record error and when writing I got a unable to set the FormulaArray property of the range class error. The formula i have is FormulaArray = _ "=INDEX('Dispatch Summary'!R[-4]C:R[4995]C[78],SMALL(IF('Dispatch Summary'!R[-4]C:R[4995]C[78]=R1C2,ROW('Dispatch Summary'!R[-4]C:R[4995]C[78])),ROW(R[-4])),2)" The formula i need is =IF(ISERROR(INDEX('Dispatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)),"zzz",INDEX('Di spatch Summary'!A1:CA5000,SMALL(IF('Dispatch Summary'!A1:CA5000=$B$1,ROW('Dispatch Summary'!A1:CA5000)),ROW(1:1)),2)) Is it possible to do? Thanks for any help. Simon |
| All times are GMT +1. The time now is 01:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com