VBA Match Array Error 1004

A

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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
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
StephenCrump said:
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
Click to expand...

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
AbdulkareemAlhassni said:
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
Click to expand...

Can you please post the code you are using?
 
Upvote 0
StephenCrump said:
Can you please post the code you are using?
Click to expand...

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
StephenCrump said:
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?
Click to expand...


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
You must log in or register to reply here.

Similar threads

A
  • Question Question
My one week effort code, can you check it up ?
Replies
2
Views
1K
AbdulkareemAlhassni
A
S
  • Question Question
Run Time Error 1004- Long Array Formula in VBA (How to split the formula)
Replies
2
Views
800
Fluff
Fluff
S
  • Question Question
Run-time error '1004
Replies
4
Views
1K
CSmith
CSmith
E
  • Question Question
Alternative formula to a multiple Index used in a 50,000 row table
Replies
2
Views
729
Fluff
Fluff
M
  • Question Question
VBA Error: Object variable or With block variable not set - while looping through directory
Replies
3
Views
4K
mikerickson
mikerickson

Forum statistics

Threads
1,223,912
Messages
6,175,338
Members
452,637
Latest member
Ezio2866

Share this page

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