=Sortby(sequence(variable),randarray(variable)) produces #NAME? in macro

[gsf314]

Hi folks,

I haven't been up here in years, and I've come across something that has me stumped.

When I enter ActiveCell.Formula2R1C1 = "=SortBy(Sequence(615),RandArray(615))" in my macro, I get a column of 615 random numbers from 1 to 615. That's what I want.

However, when I enter

LastRow = Cells(Rows.Count, 2).End(xlUp).Row
ActiveCell.Formula2R1C1 = "=SortBy(Sequence(LastRow),RandArray(LastRow)) "

in my macro I get #NAME? in the first cell of the column.

I declared LastRow as an integer, and a MsgBox shows it as 616 which is correct. I want to use a variable after Sequence and RandArray. What am I doing wrong or can't it be done?

Thanks.

Gary

 

[Fluff]

You need to do it like

ActiveCell.Formula2R1C1 = "=SortBy(Sequence(" & LastRow & "),RandArray(" & LastRow & ")) "

 

[gsf314]

You need to do it like

ActiveCell.Formula2R1C1 = "=SortBy(Sequence(" & LastRow & "),RandArray(" & LastRow & ")) "

Worked perfectly. Thanks very much. I'm really out of practice.

Gary

 

[Fluff]

You're welcome & thanks for the feedback.