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
[TABLE="width: 700"]
<tbody>[TR]
[TD]row[/TD]
[TD]b1[/TD]
[TD]c1[/TD]
[TD]d1[/TD]
[TD]e1[/TD]
[TD]f1[/TD]
[TD]g1[/TD]
[TD]h1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]00001180[/TD]
[TD]Consultant[/TD]
[TD]WK[/TD]
[TD]rate[/TD]
[TD]00001180[/TD]
[TD]00001180[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]00001190 thru 00001199[/TD]
[TD]Write in[/TD]
[TD]AR[/TD]
[TD]AR[/TD]
[TD]00001190[/TD]
[TD]00001199[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]00001271[/TD]
[TD]Power Consumption[/TD]
[TD]WK[/TD]
[TD]rate[/TD]
[TD]00001271[/TD]
[TD]00001271[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]00001290 thru 00001295[/TD]
[TD]Write In[/TD]
[TD]AR[/TD]
[TD]AR[/TD]
[TD]00001290[/TD]
[TD]00001295[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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