Multiples entry macro

J

JA

Name Entry
Wynand Botsh 3
Wynand Botsh 3
Wynand Botsh 3
James K 5
Steve Dowson 8
Kim Philips 10

I want to insert multiples of same name in a database
based on the number in a field called Entry. From the
example above James K has to be inserted 5 times before
Steve Dowson.

Please, write a macro to perform the task. I would be very
grateful.
 
D

Dave Peterson

Do you really mean it has to be inserted 5 times--for a total of 6?

So in this case, you'd end up with 38 lines:

1 existing Wynand Botsch + 3 more
1 existing " " + 3 more
1 existing James K + 5 more
.....

If yes, then how about this:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim HowMany As Variant

With ActiveSheet
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = .Cells(iRow, "B").Value
If IsNumeric(HowMany) Then
If HowMany > 0 Then
.Rows(iRow + 1).Resize(HowMany).Insert
.Cells(iRow + 1, "A").Resize(HowMany).Value _
= .Cells(iRow, "A").Value
End If
End If
Next iRow
End With
End Sub

And nothing happens to column B--not copied down--not made 1's--nothing????
 
Top