Fill series but a little different

[kingjeremy]

Hi,
ı want to fill series based on cell data, a sample is below
a1 b1
james 10
jane 8
... ..
result should be

james 1
james 2
james 3
...
james 10
jane 1
jane 2
...
jane 8

thanks for any help.

[Paul Sheppard]

Hi,
ı want to fill series based on cell data, a sample is below
a1 b1
james 10
jane 8
... ..
result should be

james 1
james 2
james 3
...
james 10
jane 1
jane 2
...
jane 8

thanks for any help.

Hi kingjeremy

Not 100% sure what you want but here goes

If James is in cell A1 and 1 is in B1, select both cells, move cursor to bottom right of selected cells, it becomes a black cross, hold down left mouse button and drag down

or if James is in Cell A1 and 10 is in B1 to fill the gaps back to James 1, you will need to insert rows above James 10 and then do the same thing but drag upwards

[Dave Peterson]

Are you working with Mellissa???

Try this against a copy of your workbook--it'll destroy the original data.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim howMany As Long

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
howMany = .Cells(iRow, "B").Value
If howMany > 1 Then
.Rows(iRow + 1).Resize(howMany - 1).Insert
.Cells(iRow, "A").Resize(howMany).Value _
= .Cells(iRow, "A").Value
With .Cells(iRow, "B").Resize(howMany)
.Formula = "=row()-" & iRow - 1
.Value = .Value
End With
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And there's no validation against the value in column B. It can blow up really
good if you don't type in nice numbers.


kingjeremy wrote:
>
> Hi,
> ı want to fill series based on cell data, a sample is below
> a1 b1
> james 10
> jane 8
> .. ..
> result should be
>
> james 1
> james 2
> james 3
> ..
> james 10
> jane 1
> jane 2
> ..
> jane 8
>
> thanks for any help.
>
> --
> kingjeremy
> ------------------------------------------------------------------------
> kingjeremy's Profile: http://www.excelforum.com/member.php...o&userid=26444
> View this thread: http://www.excelforum.com/showthread...hreadid=397155

--

Dave Peterson

[Dave Peterson]

er, Melissa???

[kingjeremy]

no I'm neither working with her nor I'm melissa. It was just a coincidence that we asked the same question almost at the same time.
And thanks for the solution it will really ease a lot of work.

[Dave Peterson]

Glad it worked for you (and Melissa, too <bg>).

kingjeremy wrote:
>
> no I'm neither working with her nor I'm melissa. It was just a
> coincidence that we asked the same question almost at the same time.
> And thanks for the solution it will really ease a lot of work.
>
> --
> kingjeremy
> ------------------------------------------------------------------------
> kingjeremy's Profile: http://www.excelforum.com/member.php...o&userid=26444
> View this thread: http://www.excelforum.com/showthread...hreadid=397155

--

Dave Peterson