autofill with max value specified

[radex7]

Hello,

I am trying to simplify my data entry and I have this idea that I do not know how to tackle, but it would be nice to have it worked out.
I am looking for an autofill macro that will fill column B2 to Bxx with values from 1 to max value specified in cell B1. Lets say I know I have 5 trees I need to enter. I want to enter "5" in a cell as a reference, and everything else will be filled in automatically with press of the button.

To make things even more productive I would like to use value from cell A2 and replicate it through the range specified above in column A2:Axx.

Result:
______ColumnA________ColumnB
Row1__[somevalue]_____[5] <<<< values specified by me.
Row2__somevalue_______1
Row3__somevalue_______2
Row4__somevalue_______3
Row5__somevalue_______4
Row6__somevalue_______5


Any ideas? Sorry about elaborate diagrams but I really had a hard time verbalizing what I need - thus no google answer...

Thanks
Radek

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

=============
How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use.

1) Put a value in A1
2) Put a number B1
3) Press Alt-F8 and select the macro from the list.

[radex7]

JBeaucaire, just plugged it in and tested - it ran beautifully - you rock - THANK YOU!

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

[radex7]

Thanks again. I came across a little glitch, that you will prob. figure out in no time... When I provided an example I provided a simplified spreadsheet hoping to update the macro accordingly. In fact, I am using other macro for this sheet to load up the data to Access, which requires fixed cell parameters (column headers are in row 3). I tried to change cell address' so they correspond to my data sheet, but the result was truncated by 2 rows. Here is the actual design of my sheet, problem remains still the same:

______ColumnA________ColumnB________ColumnC
Row1__instructions_____macro buttons
Row2__[somevalue]____[4]<<<<values specified by me (category, tree count)
Row3__header1________header2________header3
Row4__somevalue______1
Row5__somevalue______2
Row6__somevalue______3
Row7__somevalue______4

Do you think it is doable? Thanks in advance...

Radek

[JBeaucaire]

Please Login or Register  to view this content.

[radex7]

Thanks again, JBeaucaire. First post updated.