vba array issue

Sof321

New Member
Joined
May 10, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.


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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Untested, but maybe this will help. First, change the name of the destination worksheet to something other than "ERROR" which has a special place in VBA. In the snippet below I've named the sheet "ER0R". Then try this:
VBA Code:
With Worksheets("EROR")
    .Range("B3").FormulaArray = "=IFERROR(INDEX('sheet1'!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, 'sheet1'!D$2:D$10000), 0)),0)"
    .Range("B3:B300").FillDown
End With
 
Upvote 0
Welcome to the Forum!

Further to @JoeMo's comments, it's the .Selection("B3:B300") that's the problem syntax in the original post. You could also try:

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)"
    .AutoFill Destination:=Range("B3:B300"), Type:=xlFillDefault
End With
 
Upvote 0
Another approach:

VBA Code:
    Sheets("Error").Range("B3:B300").Formula = "=IFERROR(INDEX('sheet1'!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, 'sheet1'!D$2:D$10000), 0)),0)"
    Sheets("Error").Range("B3:B300").FormulaArray = Sheets("Error").Range("B3:B300").FormulaR1C1
 
Upvote 0
OOh! this now works!
But its insanely long to load.
(tell me if i should redo a post on this... i feel its related though)

i tried to make the formulas load up to last row instead of always 300 rows.

it doesnt seem to work , tells me If without for.....


VBA Code:
   Set wsErr = wkb.Sheets("ERROR")
Dim lastRow As Long, i As Long


    lastRow = wsErr.Range("B" & Rows.Count).End(xlUp).row

    With wsErr
        For i = 1 To lastRow
            If Len(Trim(.Range("A" & i).Value)) <> 0 Then _
            .Range("B3" & i).Formula = "=IFERROR(INDEX('sheet1'!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, 'sheet1'!D$2:D$10000), 0)),0)"
            .Range("B3" & i).FormulaArray = wsErr.Range("B3" & i).FormulaR1C1
            
        Next i
    End With
 
Upvote 0
You need to add an End If before the Next i
 
Upvote 0
Huh... now tells me End if without bloc if.

thank you for your kind patience, learning is a process!
 
Upvote 0
Missed that you had an underscore after then. Remove it
 
Upvote 0
Solution
different error, now says : Unable to set the formula array property of the range class.
i dont understand how to fix this...
(I hope you all know how much i learn from this. because i do. thank you for your support its so appreciated.)

(i tried .Range("B3").FormulaArray = wsErr.Range("B3" & i).FormulaR1C1, but doesnt change the result...)

VBA Code:
    With wsErr
        For i = 1 To lastRow
            If Len(Trim(.Range("A" & i).Value)) <> 0 Then
            .Range("B3").Formula = "=IFERROR(INDEX('sheet1'!D$2:D$10000, MATCH(0,COUNTIF($B$2:B2, 'sheet1'!D$2:D$10000), 0)),0)"
            .Range("B3" & i).FormulaArray = wsErr.Range("B3" & i).FormulaR1C1  'this turns yellow
            
            End If
        Next i
    End With
 
Upvote 0
i tried something else :
.Range("B3" & i).FormulaArray = wsErr.Range("B3").FormulaR1C1

didnt pop an error message but made my document unresponsive and never finished the code. :(

now i feel like there is no way out!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top