Referencing a string inside a Formula Array in Excel VBA

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

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

Next j

End Sub

There is no error; however i need This part fixed so it references the value instead of the actual word inside the formula:
IF(Sheet2!$A:$A=CustomerName

brz · Accepted Answer · 2015-07-02 07:06:27Z

1

In your case, use below:

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

in general, if you need concat string use "formula_par21""" & value & """formula_part2, if it's number - without double quotes, like "formula_par21" & value & "formula_part2

"" in VBA = " in string variable

edited Jul 2, 2015 at 7:06

answered Jul 2, 2015 at 6:57

brz

1151 silver badge7 bronze badges

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

4 Comments

Nadz Over a year ago

thank you very much this is working. I have another error now though. The part for ROW(1:1) should increase every time ROW(2,2) and so on. any idea how to fix this? I

Nadz Over a year ago

I tried using this: 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

Nadz Over a year ago

Thanks for the response; however i am getting a runtime error - object defined error 1004. Any idea?

brz Over a year ago

Where are you getting error? Which line? Post your full code in question, please.

Faddi · Accepted Answer · 2015-07-02 11:24:10Z

1

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

You gotta double the "" in the Formulas in VBA --> "" = """"

Here is a simple example why you might get the errors:

Excel Formula:

=If(A1<>"";A1;B1)

VBA Formula

"=IF(A1<>"""",A1,B1)"

so i would recommend you to try this:

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

edited Jul 2, 2015 at 11:24

answered Jul 2, 2015 at 8:35

Faddi

237 bronze badges

Collectives™ on Stack Overflow

Referencing a string inside a Formula Array in Excel VBA

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related