Pass Variable Cells into an Array Formula

Hi, I'm rather new to VBA and EXCEL, I like to think I'm not an idiot but.... I'm stumped.

I intigrated an array function i found online into a spreadsheet I'm using. I want to pass a variable cell range into that array formula and have it auto fill on my spreadsheet.

I can get the code to work with set numbers (10) but not with the variable range. See below.

Please Login or Register  to view this content.

The array function takes the range you want to randomly select from and the number of unique values you want to return.

where you see: "=randsfromrange(A1:A10,&10) I think i want it to look like "=randsfromrange(A1:A" & I & "," & I &")"

This obviously isn't working.

Any ideas?

I was hoping this is an easy fix, but if you need more info please let me know.

Thanks.

Please Login or Register  to view this content.

Thanks for all the help guys.

Pike unfortunately that code did not work. It is my understanding you can't auto fill an array formula.

Bob I was able to tweek your code a bit and did get it to work, you had a few extra " marks.

Here is the code that fixed the problem

Please Login or Register  to view this content.

Thanks again all. This is a great forum and I've only been a member 2 days :-)

......and I can't figure out how to markt his solved....

Originally Posted by ZLPROG

Bob I was able to tweek your code a bit and did get it to work, you had a few extra " marks.

I thought that & looked odd, but it was in the formula that you said worked, so I included it

Originally Posted by ZLPROG

......and I can't figure out how to markt his solved....

At the top of the threads, there is an option called Thread Tools, it should be in there.