Number each cell in a selection

[jomili]

I need some help getting the process started. I have a source workbook with Names listed in Column A of Sheet 1 from A1:A400, and in another workbook I've selected a random range,A1:K25, 275 cells. So in this instance I want to fill my range with the first 275 names from my Source workbook (if I selected a larger or smaller range of course the number would change). I thought first about doing it with a simple formula in A1 of
"=[SourceWorkbook.xlsx]sheet1!$A1",
then dragging down to the bottom of the first column. But then in column B I'd need to start with
"=[SourceWorkbook.xlsx]sheet1!$A26",
and so forth for the rest of the columns in my selection.

Then I had the bright idea of, if I could assign each cell in my selection a number, I could plug in a formula using the cell number. Only problem is I'm stumped on how to do that programmatically.

I've attached sample files of what I'm trying to do. I appreciate any help I can get.

[bakerman2]

Something to consider.

Please Login or Register  to view this content.

In your true situation you can just paste the array in your target workbook.

[jomili]

Bakerman, I tried it, but all I got was a 2 in cells B2 and D2. I came up with a start on my own, but it's far from good. IF my selection is A1 to K25, it works like a champ. But I select, say, M15:Q27, it's writing in the my original range, so I'll have to refigure it.

Please Login or Register  to view this content.

[modytrane]

One possible solution using helper cells.
First define your block size in cells M8 and M9.
I used sheets 2 for source data.
You can adjust formulas but this should get you started if you want to use formula base solution.

[modytrane]

Oh ! I just read your post.
I didn't realize you wanted to define the starting position as well. I thought it was just the size with starting position in A1.

[jomili]

I think I've solved my basic issue by giving it another try. I think this will work with any range I select, but I still need to do something if the cells in my selected range outnumber my available names.

Please Login or Register  to view this content.

[bakerman2]

Please Login or Register  to view this content.

Open Another workbook first, then open workbook with names.
Example: type 20 in first inputbox, 10 in second and A20 in third.

[jomili]

I've been testing this with my REAL name source (which contains 10,000 names, and so far it's working flawlessly, EXCEPT for converting my formulas to values. That part works fine EXCEPT when I try to fill disparate ranges. For instance, at one shot, filling ranges A1:C15, E7:G19, J4:J12. The fill works perfectly, but the value = value converts some of the values to NAs, so I'll have to rethink that.

Please Login or Register  to view this content.

[bakerman2]

Change path to Sourceworkbook.

Please Login or Register  to view this content.

[jomili]

Slick! Appears to be fast, and spot on. To make it work in my application I had to change the file path to use my actual source. This slowed it down a little, because the workbook it opens is pretty big. I'll have to work on that, but even with the size it currently is, your code is faster than mine, so i'll move in this direction. Thanks so much for the input!

Please Login or Register  to view this content.