unable to set the formula array property of the range class

B

braulitom

New Member
Joined
Feb 1, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi everyone, I'm dealing with an issue cause I can't set an array formula to a range.

This is my formula

VBA Code: 
=INDEX($AA$4:$EC$1389, MATCH($B4,$B$4:$B$1389,0), MATCH($EN$2&EN$3,$AA$2:$EC$2&$AA$3:$EC$3,0))*INDEX($M$4:$X$1389,MATCH($B4,$B$4:$B$1389,0),MATCH(LEFT($EN$2,3),$M$3:$X$3,0))

As my ranges are dynamics I managed this way on VBA

VBA Code: 
Cells(4, ColCost + 9).FormulaArray = "=INDEX($DD$4:$ " & ColCost2 & " $" & numRows & ", MATCH($B4,$B$4:$B$" & numRows & ",0), MATCH($ " & ColBeg2 & " $2& " & ColBeg2 & " $3,$DD$2:$ " & ColCost2 & " $2&$DD$3:$" & ColCost2 & "$3,0))*INDEX($M$4:$X$" & numRows & ",MATCH($B4,$B$4:$B$" & numRows & ",0),MATCH(LEFT($ " & ColBeg2 & " $2,3),$M$3:$X$3,0))"

But when I get this point, the message of the title appears.
Also I tried parting the formula into two and making a replace like below but didn't work.


VBA Code: 
Dim theFormulaPart1 As String
     Dim theFormulaPart2 As String
     theFormulaPart1 = "=INDEX($DD$4:$ " & ColCost2 & " $" & numRows & ", MATCH($B4,$B$4:$B$" & numRows & ",0),""X_X_X)"")"
                          
     theFormulaPart2 = "MATCH($ " & ColBeg2 & " $2& " & ColBeg2 & " $3,$DD$2:$ " & ColCost2 & " $2&$DD$3:$" & ColCost2 & "$3,0))*INDEX($M$4:$X$" & numRows & ",MATCH($B4,$B$4:$B$" & numRows & ",0),MATCH(LEFT($ " & ColBeg2 & " $2,3),$M$3:$X$3,0))"
     With Cells(4, ColCost + 9)
         .FormulaArray = theFormulaPart1
        .Replace """X_X_X)"")", theFormulaPart2

Please help me, I'm dealing with this issue for months.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Hi and welcome to MrExcel board!

braulitom said:
=INDEX($AA$4:$EC$1389, MATCH($B4 , $B$4:$B$1389,0)
Click to expand...
First, check your formula, you are looking for the value B4 in the range B4:B1389, this will always return row 4, since it will always find the value of B4 in the same cell B4.

Second, the length of the formula is not a problem.
You didn't put the values you have in your variables or how you declared them, so I have to assume that you are putting the wrong values in the variables.

With the following test the macro works correctly.
Note: The macro puts the same formula that you put at the beginning of your post.

You only have to consider that the variables ColCost2 and ColBeg2 are strings and must contain letters.

VBA Code: 
Sub Macro_Array_2()
  Dim ColCost As Long
  Dim numRows As Long
  Dim ColCost2 As String
  Dim ColBeg2 As String
 
  ColCost = 3
  numRows = 2000
  ColCost2 = "EC"
  ColBeg2 = "EN"

  Cells(4, ColCost + 9).FormulaArray = _
    "=INDEX($AA$4:$" & ColCost2 & "$" & numRows & ", " & _
      "MATCH($B4,$B$4:$B$" & numRows & ",0)," & _
      "MATCH($" & ColBeg2 & "$2&" & ColBeg2 & "$3,$AA$2:$" & ColCost2 & "$2&$AA$3:$" & ColCost2 & "$3,0)) * " & _
    "INDEX($M$4:$X$" & numRows & "," & _
      "MATCH($B4,$B$4:$B$" & numRows & ",0)," & _
      "MATCH(LEFT($" & ColBeg2 & "$2,3),$M$3:$X$3,0))"
End Sub

Other ways to simplify the formula:
VBA Code: 
  Cells(4, ColCost + 9).FormulaArray = _
    Replace(Replace("=INDEX($AA$4:$@$" & numRows & ", " & _
      "MATCH($B4,$B$4:$B$" & numRows & ",0)," & _
      "MATCH($#$2&#$3,$AA$2:$@$2&$AA$3:$@$3,0)) * " & _
    "INDEX($M$4:$X$" & numRows & "," & _
      "MATCH($B4,$B$4:$B$" & numRows & ",0)," & _
      "MATCH(LEFT($#$2,3),$M$3:$X$3,0))", "@", "" & ColCost2 & ""), "#", "" & ColBeg2 & "")

Or this:
VBA Code: 
  part1 = "=INDEX($AA$4:$" & ColCost2 & "$" & numRows & ", " & _
      "MATCH($B4,$B$4:$B$" & numRows & ",0)," & _
      "MATCH($" & ColBeg2 & "$2&" & ColBeg2 & "$3,$AA$2:$" & ColCost2 & "$2&$AA$3:$" & ColCost2 & "$3,0)) "
  part2 = "INDEX($M$4:$X$" & numRows & "," & _
      "MATCH($B4,$B$4:$B$" & numRows & ",0)," & _
      "MATCH(LEFT($" & ColBeg2 & "$2,3),$M$3:$X$3,0))"

  Cells(4, ColCost + 9).FormulaArray = part1 & " * " & part2

I hope it helps you.
 
Last edited:
Upvote 2
Solution
DanteAmor said:
... it will always find the value of B4 in the same cell B4.
Click to expand...
Comment1: Whilst that direct statement is true, the fact that the first B4 has no absolute row reference suggests to me that the formula might be copied down. If that is the case then for subsequent rows the match could still be at the formula row but it could instead be in some row above that since the second B4 does have an absolute row reference.

Comment2: @braulitom
Once the formula issue is resolved (& I agree with @DanteAmor that the likely issue is related to the values in your variables), since you have Office 365 the formula should not need to be entered as an array formula. Office 365 automatically attempts to evaluate all formulas as array formulas.
 
Upvote 0
braulitom said:
Hi everyone, I'm dealing with an issue cause I can't set an array formula to a range.

This is my formula

VBA Code: 
=INDEX($AA$4:$EC$1389, MATCH($B4,$B$4:$B$1389,0), MATCH($EN$2&EN$3,$AA$2:$EC$2&$AA$3:$EC$3,0))*INDEX($M$4:$X$1389,MATCH($B4,$B$4:$B$1389,0),MATCH(LEFT($EN$2,3),$M$3:$X$3,0))

As my ranges are dynamics I managed this way on VBA

VBA Code: 
Cells(4, ColCost + 9).FormulaArray = "=INDEX($DD$4:$ " & ColCost2 & " $" & numRows & ", MATCH($B4,$B$4:$B$" & numRows & ",0), MATCH($ " & ColBeg2 & " $2& " & ColBeg2 & " $3,$DD$2:$ " & ColCost2 & " $2&$DD$3:$" & ColCost2 & "$3,0))*INDEX($M$4:$X$" & numRows & ",MATCH($B4,$B$4:$B$" & numRows & ",0),MATCH(LEFT($ " & ColBeg2 & " $2,3),$M$3:$X$3,0))"

But when I get this point, the message of the title appears.
Click to expand...

If I could add my 2 cents worth, the extra spaces are causing the formula to trigger the 'Error 1004' Unable to set Formula property.

Compare the following two lines of code:

The line of code that was reporting the 'Error 1004' Unable to set Formula property:
VBA Code: 
    Cells(4, ColCost + 9).FormulaArray = "=INDEX($DD$4:$ " & ColCost2 & " $" & numRows & ", MATCH($B4,$B$4:$B$" & numRows & ",0), MATCH($ " & ColBeg2 & " $2& " & ColBeg2 & " $3,$DD$2:$ " & ColCost2 & " $2&$DD$3:$" & ColCost2 & "$3,0))*INDEX($M$4:$X$" & numRows & ",MATCH($B4,$B$4:$B$" & numRows & ",0),MATCH(LEFT($ " & ColBeg2 & " $2,3),$M$3:$X$3,0))"

The Line of code without the extra spaces that actually works:
VBA Code: 
    Cells(4, ColCost + 9).FormulaArray = "=INDEX($DD$4:$" & ColCost2 & "$" & numRows & ",MATCH($B4,$B$4:$B$" & numRows & ",0),MATCH($" & ColBeg2 & "$2&" & ColBeg2 & "$3,$DD$2:$" & ColCost2 & "$2&$DD$3:$" & ColCost2 & "$3,0))*INDEX($M$4:$X$" & numRows & ",MATCH($B4,$B$4:$B$" & numRows & ",0),MATCH(LEFT($" & ColBeg2 & "$2,3),$M$3:$X$3,0))"

This is something that I didn't learn until today. I always wondered why Array formulas that didn't hit the 255 character limit failed, perhaps this was the reason all along. :rolleyes:
 
Upvote 1
johnnyL said:
the extra spaces are causing the formula to trigger the 'Error 1004' Unable to set Formula property.
Click to expand...
Good catch, and I certainly had not noticed it, but I don't think that it is quite such a 'blanket rule'. Array formulas can have spaces in them, including @DanteAmor's formula, which worked for me, but it does depend where those spaces occur.

braulitom said:
As my ranges are dynamics I managed this way on VBA

VBA Code: 
Cells(4, ColCost + 9).FormulaArray = "=INDEX($DD$4:$ " & ColCost2 & " $" & numRows & ", MATCH($B4,$B$4:$B$" & numRows & ",0), MATCH($ " & ColBeg2 & " $2& " & ColBeg2 & " $3,$DD$2:$ " & ColCost2 & " $2&$DD$3:$" & ColCost2 & "$3,0))*INDEX($M$4:$X$" & numRows & ",MATCH($B4,$B$4:$B$" & numRows & ",0),MATCH(LEFT($ " & ColBeg2 & " $2,3),$M$3:$X$3,0))"
But when I get this point, the message of the title appears.
Click to expand...
The problem with the above line of code is that it is placing a number of spaces within range addresses, making those ranges addresses no longer rage addresses. For example, instead of creating an address like $DD$4:$EC$2000 the code is producing this text $DD$4:$ EC $2000

There are 10 such instances of incorrect spaces in that line of code (yellow below), but there are also two spaces in the formula part of the code that are fine (green)
I have put # characters in place of the spaces just to make them easier to highlight/see.

1675314383490-png.84407
 

Attachments

  • 1675314383490.png
    1675314383490.png
    14.8 KB · Views: 42
Upvote 1
DanteAmor said:
Hi and welcome to MrExcel board!


First, check your formula, you are looking for the value B4 in the range B4:B1389, this will always return row 4, since it will always find the value of B4 in the same cell B4.

Second, the length of the formula is not a problem.
You didn't put the values you have in your variables or how you declared them, so I have to assume that you are putting the wrong values in the variables.

With the following test the macro works correctly.
Note: The macro puts the same formula that you put at the beginning of your post.

You only have to consider that the variables ColCost2 and ColBeg2 are strings and must contain letters.

VBA Code: 
Sub Macro_Array_2()
  Dim ColCost As Long
  Dim numRows As Long
  Dim ColCost2 As String
  Dim ColBeg2 As String
 
  ColCost = 3
  numRows = 2000
  ColCost2 = "EC"
  ColBeg2 = "EN"

  Cells(4, ColCost + 9).FormulaArray = _
    "=INDEX($AA$4:$" & ColCost2 & "$" & numRows & ", " & _
      "MATCH($B4,$B$4:$B$" & numRows & ",0)," & _
      "MATCH($" & ColBeg2 & "$2&" & ColBeg2 & "$3,$AA$2:$" & ColCost2 & "$2&$AA$3:$" & ColCost2 & "$3,0)) * " & _
    "INDEX($M$4:$X$" & numRows & "," & _
      "MATCH($B4,$B$4:$B$" & numRows & ",0)," & _
      "MATCH(LEFT($" & ColBeg2 & "$2,3),$M$3:$X$3,0))"
End Sub

Other ways to simplify the formula:
VBA Code: 
  Cells(4, ColCost + 9).FormulaArray = _
    Replace(Replace("=INDEX($AA$4:$@$" & numRows & ", " & _
      "MATCH($B4,$B$4:$B$" & numRows & ",0)," & _
      "MATCH($#$2&#$3,$AA$2:$@$2&$AA$3:$@$3,0)) * " & _
    "INDEX($M$4:$X$" & numRows & "," & _
      "MATCH($B4,$B$4:$B$" & numRows & ",0)," & _
      "MATCH(LEFT($#$2,3),$M$3:$X$3,0))", "@", "" & ColCost2 & ""), "#", "" & ColBeg2 & "")

Or this:
VBA Code: 
  part1 = "=INDEX($AA$4:$" & ColCost2 & "$" & numRows & ", " & _
      "MATCH($B4,$B$4:$B$" & numRows & ",0)," & _
      "MATCH($" & ColBeg2 & "$2&" & ColBeg2 & "$3,$AA$2:$" & ColCost2 & "$2&$AA$3:$" & ColCost2 & "$3,0)) "
  part2 = "INDEX($M$4:$X$" & numRows & "," & _
      "MATCH($B4,$B$4:$B$" & numRows & ",0)," & _
      "MATCH(LEFT($" & ColBeg2 & "$2,3),$M$3:$X$3,0))"

  Cells(4, ColCost + 9).FormulaArray = part1 & " * " & part2

I hope it helps you.
Click to expand...
Thnak you so much!!v
 
Upvote 1
Peter_SSs said:
Good catch, and I certainly had not noticed it, but I don't think that it is quite such a 'blanket rule'. Array formulas can have spaces in them, including @DanteAmor's formula, which worked for me, but it does depend where those spaces occur.


The problem with the above line of code is that it is placing a number of spaces within range addresses, making those ranges addresses no longer rage addresses. For example, instead of creating an address like $DD$4:$EC$2000 the code is producing this text $DD$4:$ EC $2000

There are 10 such instances of incorrect spaces in that line of code (yellow below), but there are also two spaces in the formula part of the code that are fine (green)
I have put # characters in place of the spaces just to make them easier to highlight/see.

1675314383490-png.84407
Click to expand...
Thank you so much, it helped me a lot!
 
Upvote 0
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

Edgar_
  • Solved
Array formula
Replies
6
Views
349
StephenCrump
S
O
  • Question Question
Match with variable lookup array
Replies
1
Views
254
Oras78
O
S
  • Solved
Table comparison using formulas
Replies
2
Views
113
sharshra
S
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
283
etaf
etaf
S
  • Solved
Creating reusable functions with LAMBDA
Replies
5
Views
121
Fluff
Fluff

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

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