VBA or Formula to fill down sequentially every 15th row

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
35
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,207
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,541
Try:
Code:
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
How about without loop

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,810
Messages
5,525,008
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top