Fill a series stop with value of another cell

Solved/Closed
bushman - Apr 3, 2011 at 11:17 AM
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
- Apr 4, 2011 at 07:01 AM
Hello,
I wish to fill to the right, rows whose number of entries depends on a cell to the left, and whose length is therefore variable.
e.g. No plants ..... Plant no (incremented by 1 in each cell to the right).................
=== 3...........1.......2.......3
=== 7...........1.......2.......3.......4.......5.......6.......7
=== 5...........1.......2.......3.......4.......5
===11...........next blank cell/row here waiting to be filled with 11 values.
How to (macro?) autofill all the rows? - The left colulmn already has the "stop" values.)

===18...........
===34 ...........
===19...........
===27...........
etc........
Recording a macro (e.g.- select next cell down, select cell to left, select content of cell to left, Crl C, re-select start cell, Fill series, step:1, stop value: paste cell content copied...) doesn't work, because it stores the one value you select to the left first time, and repeats that as the stop value in every row.

Would much appreciate a solution. (The ancient simple macros that recorded your exact key strokes- instead of trying to interpret what they THINK you are trying to do- were a lot simpler!)
Thank you for your time.

2 replies

RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Apr 4, 2011 at 04:10 AM
Use this code

Sub fillCell()

Dim n, lastRow As Long
lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
'' supoose first row in header
For i = 2 To lastRow
n = Cells(i, 1).Value
    For j = 2 To n + 1
    Cells(i, j).Value = j - 1
    Next j
Next i
End Sub

1
Hello Womanizer,
Thankyou very much.
At first I tried to understand it, by taking bits and doing my own thing with them. (I wasn't sure where on the sheet I had to run it from, and also where the data had to be.) I couldn't get it to work, and not sure what "x1Up" does for instance, since I don't really know visual basic. Also, how to get into a place where you can enter that code you gave me. In the end, I recorded a short macro, which then allowed me to "view macro" and get onto the macro sheet. THEN I could paste your code in there and run it.)
I did a test with headings a1:a2 and some numbers in a2:a5, pasted your code and "View macros, Run fillCell()...

It works like absolute magic!
It would be nice to understand the elements of your code. I guessed it selects the range and then fills upwards? Is x1Up a VB code word? etc...
I really like this site and am happy to give the feedback following the links as in the email, and appreciate you replying so quickly.
I will now do the feedback links.
Cheers.

Oh - do you do MS Access SQL questions here also?
Bushman
0
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Apr 4, 2011 at 07:01 AM
Hi Bushman,

In this site there we are many volunteer here to help with your problem.

so also can post you MS Access SQL questions here, someone who is ace in MS Access will definately helps you.
0