VBA Match Array Error 1004

[AbdulkareemAlhassni]

Dear all

i keep getting this error with the following code

    Range("D3").Select    Selection.FormulaArray = _   
     "=IF(INDEX('Raw Data for PR Dist'!R7C5:R100000C5,MATCH(1,('PR Dist SSD Report'!RC[-3]='Raw Data for PR Dist'!R7C1:R100000C1)*('PR Dist SSD Report'!RC[-2]='Raw Data for PR Dist'!R7C2:R100000C2),0))="""","""",INDEX('Raw Data for PR Dist'!R7C5:R100000C5,MATCH(1,('PR Dist SSD Report'!RC[-3]='Raw Data for PR Dist'!R7C1:R100000C1)*('PR Dist SSD Report'!RC[-2]='Raw Data for" & _       " PR Dist'!R7C2:R100000C2),0)))" & _        ""            Range("E3").Select    Selection.FormulaArray = _        "=IF(INDEX('Raw Data for PR Dist'!R7C3:R100000C3,MATCH(1,('PR Dist SSD Report'!RC[-4]='Raw Data for PR Dist'!R7C1:R100000C1)*('PR Dist SSD Report'!RC[-3]='Raw Data for PR Dist'!R7C2:R100000C2),0))="""","""",INDEX('Raw Data for PR Dist'!R7C3:R100000C3,MATCH(1,('PR Dist SSD Report'!RC[-4]='Raw Data for PR Dist'!R7C1:R100000C1)*('PR Dist SSD Report'!RC[-3]='Raw Data for" & _        " PR Dist'!R7C2:R100000C2),0)))" & _        ""            Range("F3").Select    Selection.FormulaArray = _        "=IF(INDEX('Raw Data for PR Dist'!R7C4:R100000C4,MATCH(1,('PR Dist SSD Report'!RC[-5]='Raw Data for PR Dist'!R7C1:R100000C1)*('PR Dist SSD Report'!RC[-4]='Raw Data for PR Dist'!R7C2:R100000C2),0))="""","""",INDEX('Raw Data for PR Dist'!R7C4:R100000C4,MATCH(1,('PR Dist SSD Report'!RC[-5]='Raw Data for PR Dist'!R7C1:R100000C1)*('PR Dist SSD Report'!RC[-4]='Raw Data for" & _        " PR Dist'!R7C2:R100000C2),0)))" & _        ""            Range("J3").Select    Selection.FormulaArray = _        "=IF(INDEX('Raw Data for PR Dist'!R7C35:R100000C35,MATCH(1,('PR Dist SSD Report'!RC[-9]='Raw Data for PR Dist'!R7C1:R100000C1)*('PR Dist SSD Report'!RC[-8]='Raw Data for PR Dist'!R7C2:R100000C2),0))="""","""",INDEX('Raw Data for PR Dist'!R7C35:R100000C35,MATCH(1,('PR Dist SSD Report'!RC[-9]='Raw Data for PR Dist'!R7C1:R100000C1)*('PR Dist SSD Report'!RC[-8]='Raw Data" & _        " for PR Dist'!R7C2:R100000C2),0)))" & _        ""

Error ( 1004 VBA unable to set the formulaarray probery of the range class)

How can i fix it ?

i recorded the ( Writing the code then pasting it ) cuz i want it to automatically do it.

 

[AbdulkareemAlhassni]

Here is one way you could do this:

Dim rng As Range
Dim LastRow As Long
    
With Worksheets("PR Dist SSD Report")
    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    
    For Each rng In Range("D2:F2,J2:L2,N2:P2,S2:U2,W2:X2,Z2:AB2,AF2,AN2:AP2").Areas  ' ... etc
        Worksheets("DONT TOUCH").Range(rng.Offset(1).Address).Copy rng.Resize(LastRow - rng.Row + 1)
    Next rng

End With

my friend this is not what i need.

 

[AbdulkareemAlhassni]

Here is one way you could do this:

Dim rng As Range
Dim LastRow As Long
    
With Worksheets("PR Dist SSD Report")
    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    
    For Each rng In Range("D2:F2,J2:L2,N2:P2,S2:U2,W2:X2,Z2:AB2,AF2,AN2:AP2").Areas  ' ... etc
        Worksheets("DONT TOUCH").Range(rng.Offset(1).Address).Copy rng.Resize(LastRow - rng.Row + 1)
    Next rng

End With

i want this to be copied to specifiec cells in row 2

With Range("D3")
.FormulaArray = "=IF(A1="""","""",A1)"
.Replace "A1", "INDEX('Raw Data for PR Dist'!$E$7:$E$100000,MATCH(1,('PR Dist SSD Report'!A3='Raw Data for PR Dist'!$A$7:$A$100000)*('PR Dist SSD Report'!B3='Raw Data for PR Dist'!$B$7:$B$100000),0))"
End With




then when i autofill it to last row it will change based on Row#

 

[StephenCrump]

my friend this is not what i need.

I think it is. You just need to populate the rest of your formulae ...

Dim rng As Range
Dim LastRow As Long
    
With Worksheets("DONT TOUCH")
    With .Range("D3")
        .FormulaArray = "=IF(A1="""","""",A1)"
        .Replace "A1", "INDEX('Raw Data for PR Dist'!$E$7:$E$100000,MATCH(1,('PR Dist SSD Report'!A3='Raw Data for PR Dist'!$A$7:$A$100000)*('PR Dist SSD Report'!B3='Raw Data for PR Dist'!$B$7:$B$100000),0))"
    End With
End With

'You need to insert your formulae in E3, F3, J3 .. etc

With Worksheets("PR Dist SSD Report")
    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    
    For Each rng In Range("D2:F2,J2:L2,N2:P2,S2:U2,W2:X2,Z2:AB2,AF2,AN2:AP2").Areas  ' .. etc
        Worksheets("DONT TOUCH").Range(rng.Offset(1).Address).Copy rng.Resize(LastRow - rng.Row + 1)
    Next rng

End With

But on looking again at your original post, the formulae you have posted all appear to be INDEX/MATCH using the same MATCH?

If that's the case, you could put the MATCH into a single cell (A1, say) and reference that cell:

With Worksheets("DONT TOUCH")
    .Range("A1").FormulaArray = "=MATCH(1,('PR Dist SSD Report'!A3='Raw Data for PR Dist'!$A$7:$A$100000)*('PR Dist SSD Report'!B3='Raw Data for PR Dist'!$B$7:$B$100000),0)"
    .Range("D3").Formula = "=IF(INDEX('Raw data for PR Dist'!$E$7:$E$100000,$A1)="""","""",INDEX('Raw data for PR Dist'!$E$7:$E$100000,$A1))"
    .Range("E3").Formula = "=IF(INDEX('Raw data for PR Dist'!$C$7:$C$100000,$A1)="""","""",INDEX('Raw data for PR Dist'!$C$7:$C$100000,$A1))"
    'etc
End With

It would be nice to be able to put the one formula into the Range("D2:F2,J2:L2,N2:P2,S2:U2,W2:X2,Z2:AB2,AF2,AN2:AP2") but your column references appear a little random?