Hi to all! i am learning to use vba, and am quite unfamiliar with formula arrays to start with... so the mix causes me headaches!
please help me fix this issue.
i need to push this formula into a cell (because i delete and reinsert an actualised "sheet1" on every use, so i get #ref. my solution is to insert via VBA the formula once the new "sheet1" has been inserted. )
in the column range (B3:B300) it should look like this:
=IFERROR(INDEX(sheet1!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, sheet1!D$2:D$10000), 0)),0)
=IFERROR(INDEX(sheet1!D$2:D$10000, MATCH(0,COUNTIF($B$2:B3, sheet1!D$2:D$10000), 0)),0)
=IFERROR(INDEX(sheet1!D$2:D$10000, MATCH(0,COUNTIF($B$2:B4, sheet1!D$2:D$10000), 0)),0)
...
when i insert the formula via vba, it looks like this:
=IFERROR(INDEX(sheet1!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, sheet1!D$2:D$10000), 0)),0)
=IFERROR(INDEX(sheet1!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, sheet1!D$2:D$10000), 0)),0)
=IFERROR(INDEX(sheet1!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, sheet1!D$2:D$10000), 0)),0)
...
which doesnt work.
here is the code, ive tried many things and got a profusion of different error messages. i get an error message on this one as well (object doesnt support this property or method), but none of my many tries gave the appropriate result.
Please tell me what i should do and how to fix this, i am completely confused.
please help me fix this issue.
i need to push this formula into a cell (because i delete and reinsert an actualised "sheet1" on every use, so i get #ref. my solution is to insert via VBA the formula once the new "sheet1" has been inserted. )
in the column range (B3:B300) it should look like this:
=IFERROR(INDEX(sheet1!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, sheet1!D$2:D$10000), 0)),0)
=IFERROR(INDEX(sheet1!D$2:D$10000, MATCH(0,COUNTIF($B$2:B3, sheet1!D$2:D$10000), 0)),0)
=IFERROR(INDEX(sheet1!D$2:D$10000, MATCH(0,COUNTIF($B$2:B4, sheet1!D$2:D$10000), 0)),0)
...
when i insert the formula via vba, it looks like this:
=IFERROR(INDEX(sheet1!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, sheet1!D$2:D$10000), 0)),0)
=IFERROR(INDEX(sheet1!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, sheet1!D$2:D$10000), 0)),0)
=IFERROR(INDEX(sheet1!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, sheet1!D$2:D$10000), 0)),0)
...
which doesnt work.
here is the code, ive tried many things and got a profusion of different error messages. i get an error message on this one as well (object doesnt support this property or method), but none of my many tries gave the appropriate result.
Please tell me what i should do and how to fix this, i am completely confused.
VBA Code:
With wkb.Worksheets("ERROR")
.Range("B3").FormulaArray = "=IFERROR(INDEX('sheet1'!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, 'sheet1'!D$2:D$10000), 0)),0)"
.Selection("B3:B300").AutoFill Destination:=Range("B3:B300"), Type:=xlFillDefault
End With