Deploy VBA Cell Reference in Array Formula

Question

So I'm trying to do a couple things with this subroutine but can't get VBA to execute the .FormulaArray function.

Create a named range using offset & lastrow function
Use cell references to insert into the array formula

--

Sub namedrange()
    Dim firstrow As Long
    Dim LastRow As Long
    Dim ColToLetter, absolute, Title, mc, mc1

    ActiveCell.Offset(0, -1).Select
    absolute = ActiveCell.Address
    LastRow = ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
    firstrow = ActiveCell.Row
    ColLetter = Mid(ActiveCell.Address, 2, 1)
    ActiveSheet.Range(ColLetter & firstrow & ":" & ColLetter & LastRow).Name = Range(ColLetter & "1").Value
    Title = Range(ColLetter & "1").Value
    ActiveCell.Offset(0, 1).Select
    mc = ActiveCell.Offset(-1, 0).Address
    mc = Mid(mc, 2, 3)
    mc1 = Replace(mc, "$", "")

    ActiveCell.FormulaArray= "=IF(ROWS(mc & "":"" & mc1)>SUM(IF(FREQUENCY(IF(Title<>"""",MATCH(Title,Title,0)),ROW(Title)-ROW(absolute)+1),1)),"""",INDEX(Title,SMALL(IF(FREQUENCY(IF(Title<>"""",MATCH(Title,Title,0)),ROW(Title)-ROW(absolute)+1),ROW(Title)-ROW(absolute)+1),ROWS(mc & "":"" & mc1))))"
End Sub

The formula bar shows what the vba function is outputting, which is not what I want. I don't know why it won't output the references I've created like mc should be "$A$2" not "mc".

Also when I try to execute the FormulaArray code I get a runtime error 1004 "Unable to set the FormulaArray property of the Range class"

In your statement Rows(mc & "":"" & mc1) Your double quotes get counted as two empty strings. True using `CHR(34) to get quotes in your string. — Kyle

Matteo NNZ Matteo NNZ · Accepted Answer · 2015-03-02T19:56:00

Your .FormulaArray content has some typos. Here's how it should look like (assuming all the above code is fine):

ActiveCell.FormulaArray= "=IF(ROWS(" & mc & ":" & mc1 & ")>SUM(IF(FREQUENCY(IF(Title<>" & chr(34) & chr(34) & ",MATCH(Title,Title,0)),ROW(Title)-ROW(absolute)+1),1))," & chr(34) & chr(34) & ",INDEX(Title,SMALL(IF(FREQUENCY(IF(Title<>" & chr(34) & chr(34) & ",MATCH(Title,Title,0)),ROW(Title)-ROW(absolute)+1),ROW(Title)-ROW(absolute)+1),ROWS(" & mc & ":" & mc1 & "))))"

In general, remember that if you want the value of a variable to be printed into a string, you cannot write "a=mc+3" but rather a = " & mc & "+3".

Deploy VBA Cell Reference in Array Formula

1 Answers