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
– Kyle, Commented Mar 2, 2015 at 19:43

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

0

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".

answered Mar 2, 2015 at 19:56

Matteo NNZ

12.8k13 gold badges62 silver badges104 bronze badges

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

5 Comments

Zebra Over a year ago

Ahhh that's why it wasn't working, i'll have to try that & see how it works. Thank you all.

Zebra Over a year ago

Okay so how would I print the value of the other references in a string? IF(&Title&<> I have "Title" & "absolute" as references which I can't print

Matteo NNZ Over a year ago

@Zebra the logic is always the same. If you want the value of the variable, you always have to nest it as I showed you above: "IF(" & Title & "<>"

Matteo NNZ Over a year ago

A trick that I always use: position your cursor in the point in which you want the value to appear, then type ", &, &, ". Position your cursor in between of the two & and type your variable.

Matteo NNZ Over a year ago

@Zebra if it works, please accept the answer. It will tell other users that this is the solution to your problem

Collectives™ on Stack Overflow

Deploy VBA Cell Reference in Array Formula

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related