Fill a series stop with value of another cell

[Solved/Closed]
Report
-
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
-
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

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

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
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
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.