vba array issue

[Sof321]

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.

  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

 

[johnnyL]

I see you still are not satisfied, so I whipped up another version based upon your original post here. After that you can make changes that you see fit. It gets the lastrow of used data from Column B (instead of the hard coded 300), and puts the array formulas in the column B, starting at row 3.

Sub TestLoadFormulasToArray()
'
    Dim DataStartRow            As Long
    Dim ERROR_ColumnB_LastRow   As Long
    Dim FormulaNumber           As Long
    Dim FormulaArray()          As Variant

    DataStartRow = 3
'
    With Worksheets("ERROR")
        ERROR_ColumnB_LastRow = .Range("B" & Rows.Count).End(xlUp).Row                                  '   Get last row used in Sheets("ERROR") column B
'
        ReDim FormulaArray(1 To ERROR_ColumnB_LastRow - DataStartRow + 1)                               '   Set # of rows for the 1D zero based FormulaArray
'
        For FormulaNumber = 1 To UBound(FormulaArray)                                                   '   Loop to put formulas into FormulaArray
            FormulaArray(FormulaNumber) = "=IFERROR(INDEX(sheet1!D$2:D$10000, MATCH(0,COUNTIF($B$2:B" & _
                    FormulaNumber + 1 & ", sheet1!D$2:D$10000), 0)),0)"                                 '       Save Formula into FormulaArray
        Next                                                                                            '   Loop back
'
        .Range("B" & DataStartRow).Resize(UBound(FormulaArray)) = Application.Transpose(FormulaArray)   '   Display FormulaArray to Sheets("ERROR")
'
        .Range("B" & DataStartRow & ":B" & ERROR_ColumnB_LastRow).FormulaArray = _
                .Range("B" & DataStartRow & ":B" & ERROR_ColumnB_LastRow).FormulaR1C1                   '   Convert formulas to Array formulas
    End With
'
    MsgBox "Completed."
End Sub

 

[StephenCrump]

.Range("B3" & i).FormulaArray = wsErr.Range("B3").FormulaR1C1

You've got your row referencing wrong here, and in the previous post. In the first iteration of the loop, when i = 1, .Range("B3" & i) will refer to B31 etc.

Writing to Excel once cell at a time, as you're doing in Post #9, is very slow. @johnnyL has taken the calculations into a VBA array, which will be faster.

Alternatively, would adapting the original approach like this work for you?

'Given you are populating column B, should the line below be referring to column "A" instead?
lastRow = wsErr.Range("B" & Rows.Count).End(xlUp).Row

With wsErr.Range("B3")
    .FormulaArray = "=IF(len(TRIM(A3)),IFERROR(INDEX('sheet1'!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, 'sheet1'!D$2:D$10000), 0)),0),"""")"
    .AutoFill Destination:=Range("B3:B" & lastRow), Type:=xlFillDefault
End With