VBA or Formula to fill down sequentially every 15th row

[Dustinkli]

Hello everyone.

I have spent 2 days trying to figure this one out but I feel like I'm at a dead end.

I need a VBA or Formula to fill down sequentially from every 15th row.

In this case I have a list of IP addresses I'm trying to generate and it looks like this:

Column E
Row 3 - 111.111.111.5

Rows 4 to 16 are blank

Row 17 - 222.444.555.81

Rows 18 to 30 are blank

Row 31 - 23.445.112.67

Etc.

(These ips are made up just for the example)

What I need to do is generate 13 sequential IPs based on each IP in column E. I'd like them in Column F and to start on row 4.

So I need to get :
Column F
Row 4 - 111.111.111.6
Row 5 - 111.111.111.7
Row 6 - 111.111.111.8
Row 7 - 111.111.111.9
Etc. Until row 16 - 111.111.111.18

Then on row 18 in Column F: 222.444.555.82
Row 19 - 222.444.555.83
Etc.

I have hundreds and hundreds of IPs so manually dragging them down and moving them over to column F would take forever.

I've tried numerous methods but I can't get it to work like I'm trying because when referencing it, because E4 is blank.

 

[Fluff]

How about

Sub Dustinkli()
   Dim Rng As Range
   Dim i As Long
   Application.ScreenUpdating = False
   For Each Rng In Range("E3:E" & Rows.Count).SpecialCells(xlConstants).Areas
      For i = 1 To 13
         Rng(i).Offset(1, 1).Value = Left(Rng, InStrRev(Rng, ".")) & Split(Rng, ".")(3) + i
      Next i
   Next Rng
End Sub

This assumes that the final octet will never exceed 255

 

[mumps]

Try:

Sub dustinkli()
    Application.ScreenUpdating = False
    Dim LastRow As Long, x As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = 3 To LastRow Step 14
        With Range("F" & x)
            .Value = .Offset(0, -1)
            .AutoFill Destination:=Range("F" & x).Resize(14, 1), Type:=xlFillSeries
        End With
        Range("F" & x).ClearContents
    Next x
    Application.ScreenUpdating = True
End Sub

 

[Dustinkli]

Thank you Fluff and Mumps. They actually both worked perfectly.

 

[DanteAmor]

How about without loop

Sub Macro15()
Dim lr As Long
lr = Range("E" & Rows.Count).End(xlUp).Row
Range("E4:E" & lr).Copy Range("F4")
With Range("F4:F" & lr).Resize(lr + 13)
  .SpecialCells(xlCellTypeBlanks) = "=LEFT(R[-1]C,SEARCH("" "",SUBSTITUTE(R[-1]C,""."","" "",3)))&VALUE(TRIM(RIGHT(SUBSTITUTE(R[-1]C,""."",""      ""),6))+1)"
  .Value = .Value
End With
End Sub

 

[Fluff]

Thank you Fluff and Mumps. They actually both worked perfectly.

Glad we could help & thanks for the feedback