Variable in Array returns "Unable to set the FormulaArray property of the Range class"

Question

I have a section in my current vba that goes through 10000 rows within an array formula and, within a table on another tab, it places cell values based on their position to a lined header...the code looks like this:

Selection.FormulaArray = _
    "=INDEX('Sheet1'!R1C1:R10000C2,SMALL(IF(ISNUMBER(SEARCH(""*__________*"",'Sheet1'!R1C1:R10000C2)),ROW('Sheet1'!R1C1:R10000C2)),ROW(R[-8]))+1,1)"

This lined header length changes between data, which is why I have to do the 'contains' vs. 'equals'. This is working for me, but it is taking awhile to run and I was hoping to reduce its run time. When I copy data into Sheet1, most of the time, it is far less than 10000 rows (but I need it that high for some data). I was trying to use a variable with UsedRange, but when I put that in the array formula, I get the error in the title. Here's my code with the variable:

Dim LR As Long
LR = Worksheets("Sheet1").UsedRange.Rows.Count

Selection.FormulaArray = _
    "=INDEX('Sheet1'!R1C1:R1C2 & LR,SMALL(IF(ISNUMBER(SEARCH(""*__________*"",'Sheet1'!R1C1:R1C2 & LR)),ROW('Sheet1'!R1C1:R1C2 & LR)),ROW(R[-8]))+1,1)"

Can anyone help me understand why I get this error? Thanks.

Shai Rado · Accepted Answer · 2018-04-05 17:53:01Z

1

You need to take LR outside the " every time you was it as a reference to last row.

Also, you just placed LR at the end, it's needs to replace the constant "R10000C2" with "R" & LR & "C2".

Selection.FormulaArray = _
    "=INDEX('Sheet1'!R1C1:R" & LR & "C2 ,SMALL(IF(ISNUMBER(SEARCH(""*__________*"",'Sheet1'!R1C1:R" & LR & "C2)),ROW('Sheet1'!R1C1:R" & LR & "C2)),ROW(R[-8]))+1,1)"

answered Apr 5, 2018 at 17:53

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Variable in Array returns "Unable to set the FormulaArray property of the Range class"

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related