AutoFill to every 3rd row

[Zacariah171]

My work uses spreadsheets to display the current inventory for companies we rent to. The way they have it formatted is, starting in A56, they'll have "Box 1", in A57 they'll have the serial number of the equipment, and then A58 will be blank. A59 will be Box 2, A60 will be that serial number, and then A61 will be blank. This may go on until Box 60 or more, depends on which company's sheet it is. Below this list, in the same column, will start a new list of Spare equipment. This list will be in the same format but instead of "Box 1", it'll be "Spare 1". Rows will get added and deleted as equipment gets shipped or returned, so it would be nice to add a button that will run a macro to renumber the inventory. I tried just using formulas in the cells but they would break when a cell or row got deleted due to reference errors.

Box 1
155

Box 2
199

Box 3
057


Spare 1
706

Spare 2
171

What I've been trying to do is to use VBA to either use an autofill function or a loop to fill column A of every 3rd row, starting from A56, with Box 1, Box 2, etc until the cell value equals Spare. Once I get to the Spare list, I'd like to do the exact same thing until it reaches a cell where the value is Stop. I've tried many different ways but just can't seem to get it right. I'm very new to this, so I don't know if a loop would be better or an autofill would be better but everything I'm trying is failing. I apologize if I'm not explaining this clearly. Any help is appreciated. Thank you in advance.

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub NumberBoxesAndSpares()
  Dim RowNum As Long, Counter As Long
  RowNum = Columns("A").Find("Box", , xlValues, xlPart, , xlNext, False, , False).Row
  Counter = 1
  Do While Cells(RowNum, "A").Value Like "Box*"
    Cells(RowNum, "A").Value = "Box " & Counter
    Counter = Counter + 1
    RowNum = RowNum + 3
  Loop
  RowNum = Columns("A").Find("Spare", , xlValues, xlPart, , xlNext, False, , False).Row
  Counter = 1
  Do While Cells(RowNum, "A").Value Like "Spare*"
    Cells(RowNum, "A").Value = "Spare " & Counter
    Counter = Counter + 1
    RowNum = RowNum + 3
  Loop
End Sub[/td]
[/tr]
[/table]

 

[Zacariah171]

That works perfect. Thank you so much!