Insert a variable number of rows based on the numerical value of a cell.

[jackrobat]

Below is an example of a text based spreadsheet we use for cross referencing valid cost codes. I am trying to eliminate the Write-in range of numbers and assign actual rows for each of those numbers so they can be used in a look up table. I added column F as LEFT formula and Column H as a RIGHT Formula to isolate the numbers so I could perform the difference calc in Col H.

Ideally, I'd like to insert the correct number of rows below the first write in and number them accordingly, Big Bonus if I could include the title "Write In" into column C.

I am using Excel 2013

row	b1	c1	d1	e1	f1	g1	h1
20	00001180	Consultant	WK	rate	00001180	00001180	0
21	00001190 thru 00001199	Write in	AR	AR	00001190	00001199	9
50	00001271	Power Consumption	WK	rate	00001271	00001271	0
51	00001290 thru 00001295	Write In	AR	AR	00001290	00001295	5

<tbody>
</tbody>

 

[Rijnsent]

Hi Jackrobat,
it sounds like your easiest solution would be a macro that does this for you. What is the end result you are looking for? Just a row of account numbers?
Cheers,
Koen

 

[MickG]

Try this:- for results on sheet2 based on Data in sheet1

[COLOR="Navy"]Sub[/COLOR] MG20Jul41
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
Ray = Sheets("Sheet1").Range("A1").CurrentRegion
 ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 1), 1 To UBound(Ray, 2))


[COLOR="Navy"]For[/COLOR] Rw = 2 To UBound(Ray, 1)
    [COLOR="Navy"]If[/COLOR] InStr(Ray(Rw, 2), "thru") > 0 [COLOR="Navy"]Then[/COLOR]
        Sp = Split(Ray(Rw, 2), "thru")
        [COLOR="Navy"]For[/COLOR] nRw = Val(Trim(Sp(0))) To Val(Trim(Sp(UBound(Sp))))
            c = c + 1
            [COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
                nray(c, Ac) = IIf(Ac = 2, "0000" & nRw, Ray(Rw, Ac))
            [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]Next[/COLOR] nRw
   [COLOR="Navy"]Else[/COLOR]
            c = c + 1
            [COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
                nray(c, Ac) = Ray(Rw, Ac)
            [COLOR="Navy"]Next[/COLOR] Ac
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, UBound(Ray, 2))
   .Parent.Range("B:B,F:F,G:G").NumberFormat = "@"
   .Value = nray
   .Columns.AutoFit
   .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jackrobat]

Try this:- for results on sheet2 based on Data in sheet1

[COLOR="Navy"]Sub[/COLOR] MG20Jul41
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
Ray = Sheets("Sheet1").Range("A1").CurrentRegion
 ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 1), 1 To UBound(Ray, 2))


[COLOR="Navy"]For[/COLOR] Rw = 2 To UBound(Ray, 1)
    [COLOR="Navy"]If[/COLOR] InStr(Ray(Rw, 2), "thru") > 0 [COLOR="Navy"]Then[/COLOR]
        Sp = Split(Ray(Rw, 2), "thru")
        [COLOR="Navy"]For[/COLOR] nRw = Val(Trim(Sp(0))) To Val(Trim(Sp(UBound(Sp))))
            c = c + 1
            [COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
                nray(c, Ac) = IIf(Ac = 2, "0000" & nRw, Ray(Rw, Ac))
            [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]Next[/COLOR] nRw
   [COLOR="Navy"]Else[/COLOR]
            c = c + 1
            [COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
                nray(c, Ac) = Ray(Rw, Ac)
            [COLOR="Navy"]Next[/COLOR] Ac
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, UBound(Ray, 2))
   .Parent.Range("B:B,F:F,G:G").NumberFormat = "@"
   .Value = nray
   .Columns.AutoFit
   .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Thanks I will give this a try. I ended up writing something that sort of added the rows then used several "if" macros to populate a column but I am finding errors here and there.
I'll try yours out on the original and see if it cleans things up for me.
Thanks again