Working with IP Addresses

[michael8000]

Does anyone have a working VB script to work with IP ranges?

For example. I need to "input" the following three IP ranges into Excel and have it converted to single IPs.

These as an input:
10.10.30.92 - 10.10.30.95
10.10.64.158 - 10.10.64.161
10.10.66.76 - 10.10.66.77

Would output this:
10.10.30.92
10.10.30.93
10.10.30.94
10.10.30.95
---
10.10.64.158
10.10.64.159
10.10.64.160
10.10.64.161
---
10.10.66.76
10.10.66.77


I have over 1000 IP ranges that need to be converted to single IPs

Thank you for your help!

 

[Fluff]

How about

Sub SplitIP()
   Dim Ip As Variant
   Dim Cl As Range
   Dim i As Long, Rw As Long
   
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      Ip = Split(Cl.Value, "-")
      For i = Split(Ip(0), ".")(3) To Split(Ip(1), ".")(3)
         Rw = Rw + 1
         Range("C" & Rw).Value = Left(Ip(0), InStrRev(Ip(0), ".")) & i
      Next i
   Next Cl
End Sub

 

[Scott Huish]

Is the last octet the only value incrementing or would you have something like this?

10.10.29.92 - 10.10.30.95

 

[michael8000]

The 3rd octet is also a factor... that's what makes it complicated

 

[Fluff]

If the 3rd octet only changes by one, you could try this

Sub SplitIP()
   Dim Ip As Variant
   Dim Cl As Range
   Dim i As Long, j As Long, Rw As Long
   
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      Ip = Split(Cl.Value, "-")
      If Split(Ip(0), ".")(2) = Split(Ip(1), ".")(2) Then
         For i = Split(Ip(0), ".")(3) To Split(Ip(1), ".")(3)
            Rw = Rw + 1
            Range("C" & Rw).Value = Left(Ip(0), InStrRev(Ip(0), ".")) & i
         Next i
      Else
         For i = Split(Ip(0), ".")(3) To 255
            Rw = Rw + 1
            Range("C" & Rw).Value = Left(Ip(0), InStrRev(Ip(0), ".")) & i
         Next i
         For i = 0 To Split(Ip(1), ".")(3)
            Rw = Rw + 1
            Range("C" & Rw).Value = Trim(Left(Ip(1), InStrRev(Ip(1), "."))) & i
         Next i
      End If
   Next Cl
End Sub

I've assumed that the final octet ranges form 0 to 255

 

[michael8000]

Yes...0 to 255

 

[Fluff]

This should hopefully work for anything

Sub SplitIP()
   Dim Ip As Variant
   Dim Cl As Range
   Dim i As Long, j As Long, Rw As Long
   
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      Ip = Split(Cl.Value, "-")
         For i = Split(Ip(0), ".")(2) To Split(Ip(1), ".")(2)
            For j = IIf(Split(Ip(0), ".")(2) = i, Split(Ip(0), ".")(3), 0) To IIf(Split(Ip(1), ".")(2) = i, Split(Ip(1), ".")(3), 255)
            Rw = Rw + 1
            Range("C" & Rw).Value = Split(Ip(0), ".")(0) & "." & Split(Ip(0), ".")(1) & "." & i & "." & j
         Next j
      Next i
   Next Cl
End Sub

 

[michael8000]

This should hopefully work for anything

Sub SplitIP()
   Dim Ip As Variant
   Dim Cl As Range
   Dim i As Long, j As Long, Rw As Long
   
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      Ip = Split(Cl.Value, "-")
         For i = Split(Ip(0), ".")(2) To Split(Ip(1), ".")(2)
            For j = IIf(Split(Ip(0), ".")(2) = i, Split(Ip(0), ".")(3), 0) To IIf(Split(Ip(1), ".")(2) = i, Split(Ip(1), ".")(3), 255)
            Rw = Rw + 1
            Range("C" & Rw).Value = Split(Ip(0), ".")(0) & "." & Split(Ip(0), ".")(1) & "." & i & "." & j
         Next j
      Next i
   Next Cl
End Sub

Wow...this is exactly what I was looking for! Thank you so much!!! Works perfect!!!

 

[Fluff]

Glad to help & thanks for the feedback