Referencing a string within an Index Formula Array in Excel VBA ROW(1:1)

Question

Need some help referencing a string in an Index formula array

My code below:

Sub Loop_Test2()

Dim i As Long
Dim j As Long
Dim CountAll As Long
Dim CountXL As Long
Dim CustomerName As String

ActiveSheet.Range("A1").Activate

CountAll = ActiveSheet.Range("A35")

For j = 1 To CountAll
i = 2

CountXL = Cells(i, j).Value
R = 1
For i = 1 To CountXL
CustomerName = Cells(1, j).Value
'MsgBox CustomerName
MsgBox R
Cells(i + 2, j).FormulaArray = "=IFERROR(INDEX(Sheet2!$A:$B,SMALL(IF(Sheet2!$A:$A=""" & CustomerName & """,ROW(Sheet2!$A:$A)),ROW(R:R))*1,2),0)"
R = R + 1
Next i
Next j
End Sub

I am trying to put a reference in this part:

ROW(1:1)

change it to:

ROW(""" & R & """ : """ & R & """)

However receiving an object error 1004

brz · Accepted Answer · 2015-07-02 08:51:22Z

1

Delete double quotes

ROW(" & R & " : " & R & ")

Full:

Cells(i + 2, j).FormulaArray = "=IFERROR(INDEX(Sheet2!$A:$B,SMALL(IF(Sheet2!$A:$A=""" & CustomerName & """,ROW(Sheet2!$A:$A)),ROW(" & R & ":" & R & "))*1,2),0)"

Example to understand:

a = 10
b = "sometext_" & a & "_sometext"

?b in immediate window (Ctrl+G) will print:

sometext_10_sometext

b = "sometext_""" & a & """_sometext"

will print:

sometext_"10"_sometext

Double quotes in editor goes as quote in variable.

answered Jul 2, 2015 at 8:51

brz

1151 silver badge7 bronze badges

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

Collectives™ on Stack Overflow

Referencing a string within an Index Formula Array in Excel VBA ROW(1:1)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related