VBA Match Array Error 1004

AbdulkareemAlhassni

New Member
Joined
Nov 16, 2018
Messages
41
Dear all

i keep getting this error with the following code

PHP:
    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.
 
Last edited:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Your formulae haven't posted very well, but I think what you've got is:

Range("D3").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)))"

with similar formulae for E3, F3 and J3.

The problem you have is that .FormulaArray can only handle strings up to 255 characters.

The way around it is to build up the formula in steps.

I'm not sure why your formula are in R1C1 notation. If you are using R1C1, try:

Code:
With Range("D3")
    .FormulaArray = "=IF(R1C1="""","""",R1C1)"
    .Replace "R1C1", "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))"
End With

Or if you are using A1 referencing, try:
Code:
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
 
Upvote 0
Your formulae haven't posted very well, but I think what you've got is:

Range("D3").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)))"

with similar formulae for E3, F3 and J3.

The problem you have is that .FormulaArray can only handle strings up to 255 characters.

The way around it is to build up the formula in steps.

I'm not sure why your formula are in R1C1 notation. If you are using R1C1, try:

Code:
With Range("D3")
    .FormulaArray = "=IF(R1C1="""","""",R1C1)"
    .Replace "R1C1", "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))"
End With

Or if you are using A1 referencing, try:
Code:
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

Please check my entire code and tell me how to improve,

i have avoided array formula and used copy paste around 39 times instead ... which is a stupid way but its the only way i know..

https://www.mrexcel.com/forum/excel...effort-code-can-you-check-up.html#post5326631

this is the link.
 
Upvote 0
I tested this, it works but the problem is when i do it for a range of cells it keeps copying the same refrenced data cell

it doesnt change the refrence cell A3 and B3 to A4 and B4... as my range is to the last row.count
 
Upvote 0
I tested this, it works but the problem is when i do it for a range of cells it keeps copying the same refrenced data cell

it doesnt change the refrence cell A3 and B3 to A4 and B4... as my range is to the last row.count
 
Upvote 0
I tested this, it works but the problem is when i do it for a range of cells it keeps copying the same refrenced data cell

it doesnt change the refrence cell A3 and B3 to A4 and B4... as my range is to the last row.count

Can you please post the code you are using?
 
Upvote 0
Can you please post the code you are using?

currently am copying the formula which i have used ( INDIRECT : to keep the refrence cells : ) from another worksheet

this is the formula being used as i did not know how to use Array for copying.


Code:
'First Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("D3:F3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("D2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("D2:F" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select



its repeated 14 times due to many columns to copy their first cell.
 
Upvote 0
This works for me, and updates A3 and B3 to A4 and B4 etc, as you require:

Code:
Dim LastRow As Long

LastRow = Worksheets("PR Dist SSD Report").Range("A" & Rows.Count).End(xlUp).Row

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
    'you can do similar for E3 and F3, then ..
    .Range("D3:F3").Copy Worksheets("PR Dist SSD Report").Range("D2:F" & LastRow)
End With

Is this what you need?
 
Upvote 0
This works for me, and updates A3 and B3 to A4 and B4 etc, as you require:

Code:
Dim LastRow As Long

LastRow = Worksheets("PR Dist SSD Report").Range("A" & Rows.Count).End(xlUp).Row

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
    'you can do similar for E3 and F3, then ..
    .Range("D3:F3").Copy Worksheets("PR Dist SSD Report").Range("D2:F" & LastRow)
End With

Is this what you need?


Code:
'First Copy for INDEX Lookup    Sheets("DONT TOUCH").Select
    Range("D3:F3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("D2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("D2:F" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
    




    
 'Second Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("J3:L3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("J2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("J2:L" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
    
       


 'Third Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("N3:P3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("N2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("N2:P" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
    
    
     'Foruth Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("S3:U3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("S2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("S2:U" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select




     'Fifth Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("W3:X3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("W2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("W2:X" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select




     'Sixth Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("Z3:AB3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("Z2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("Z2:AB" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select




     'Sivinth Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("AF3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("AF2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("AF2:AF" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select




     'eigth Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("AN3:AP3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("AN2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("AN2:AP" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select






     'Ninghth Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("AR3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("AR2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("AR2:AR" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select






     'Tenth Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("AU3:AX3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("AU2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("AU2:AX" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select










'gap






     'eleventh Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("BG3:BJ3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("BG2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("BG2:BJ" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select




     'twelve Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("BL3:BO3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("BL2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("BL2:BO" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select


     'Thirteen Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("BR3:BU3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("BR2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("BR2:BU" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select




     'Fourteen Copy for INDEX Lookup
    Sheets("DONT TOUCH").Select
    Range("BW3").Select
    Selection.Copy
    Sheets("PR Dist SSD Report").Select
    Range("BW2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("BW2:BW" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

this is the actual code. i think it would make things clearer.

i am copying from another sheet, i want instead to have the code in VBA and paste it for each one of these cells,

what you did works but how can i make it work for all of these without repeating it many times ?
 
Last edited:
Upvote 0
Here is one way you could do this:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,832
Messages
6,181,235
Members
453,026
Latest member
cknader

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