Fill series but a little different

K

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.
 
P

Paul Sheppard

kingjeremy said:
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.
Click to expand...

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 curso
to bottom right of selected cells, it becomes a black cross, hold dow
left mouse button and drag down

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

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.
 
K

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.
 
D

Dave Peterson

Glad it worked for you (and Melissa, too <bg>).
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.
Click to expand...
 
Top