Need VBA Code to Copy Predefined Text for each one IPv4 Address that can be found in 1st S

Hello Dear Excel Users,

I Am trying long time ago now to implement an XLSM file that Will create a script for creating LSPs for my LAB (Routers)

The XLSM view is as above



Actually in 1st XLSM Sheet Columns (A,B,C,D,E,F,G,H,I.J,K) are the data entered from another XLS and with colors (are being verified with conditional formating that are correct.

On Columns (M,N,O.P) are only the IPv4 addresses that will be used on the next part so to create a CLI script (for creating the LSPs in my LAB)

The case is that i nead for each one cell of Columns (M,O) that containts an IPv4 like (172.17.x.x) a predifined CLI code to be writen to another sheet of XLSM by adding this IPv4 Address. (See example bellow).

Example for 3xIP addresses found on Columns (M,O) it should create 7xLine code (Starting from MACRO_TE_TUNNEL_PATH_HOP NoCmd_TE_TUNNEL_PATH_HOP) and Ending in "ENDM" by adding also the IPv4 address at field "NEXT_HOP_IP = 172.17.X.X" (See example bellow).



The problem is that since on the 1st image as you can see some IPv4 addresses will be Null (No data) so VBA should delete 7x Lines CLI code for each one that will not find an IPv4 address (See example bellow - XLSM lines from 151 to 157 should be removed from VBA since no Valid IPv4 address found).



Could you please help me? I am really stacked!

Actually the needed VBA code should find from Sheet named as "XXX" the field that contains null IPv4 address "NEXT_HOP_IP = " delete it and also delete 3x Rows above and 3x Rows bellow

I found some other code (like the above) but i cannot make it work as i cannot filter and separate the wording "NEXT_HOP_IP = " with the "NEXT_HOP_IP = 172.17.x.x"

Your help is really appreciated.

Scenario1 (not match my needs - but is close relative):

Sub deleteRows()
'
' deleteRows Macro
'
' Keyboard Shortcut: Ctrl+s
'
Dim StartRange As String
Dim EndRange As String
Cells.Find(What:="NEXT_HOP_IP = ").Select
StartRange = ActiveCell.Address
EndRange = ActiveCell.Address & 4
ActiveSheet.Range(StartRange & ":" & EndRange).Select
Selection.Delete Shift:=xlUp

End Sub

Scenario2 (not match my needs - but is close relative):

Sub Delete_Rows()

Dim xRow As Integer
Dim strSearch As String

strSearch = "NEXT_HOP_IP = "
' Assuming Total is in column A as your picture shows, but you can configure to search anywhere

xRow = Range("A" & Rows.Count).End(xlUp).Row
Range("$A1:C" & xRow).Select

Selection.find(What:=strSearch, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

Range(ActiveCell.Row & ":" & ActiveCell.Offset(-3, 0).Row).Select
Selection.Delete Shift:=xlUp

End Sub

Scenario3 (not match my needs - but is close relative):

Sub DeleteRows()
Dim last As Long
Dim i As Long
With ActiveSheet
last = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = last To 1 Step -1 'Changed that from the original code!!!
If .Cells(i, 1).Value Like "NEXT_HOP_IP = 172*" Then
.Cells(i - 7, 1).Resize(6, 1).EntireRow.Delete
End If
Next i
End With
End Sub

Scenario4 (not match my needs - but is close relative):

Sub test()

Dim currentSht As Worksheet
Dim startCell As Range
Dim lastRow As Long
Dim lastCol As Long
Dim i as integer

Set currentSht = ActiveWorkbook.Sheets(1)

Set startCell = currentSht.Range("A1")
lastRow = startCell.SpecialCells(xlCellTypeLastCell).Row
lastCol = startCell.SpecialCells(xlCellTypeLastCell).Column

For i = lastRow To 1 Step - 1
If Cells(i, "B").Value <> "NEXT_HOP_IP = 172.17*" Then
Range(Cells(i, "B").Offset(1), Cells(i, "B").Offset(2)).EntireRow.Delete 'delete two below
Cells(i, "B").Offset(-1).EntireRow.Delete ' delete one above


End Sub

Scenario5 (not match my needs - but is close relative):

Sub Delete_Rows_Based_On_Criteria()

Dim newUpRow, newDownRow As Integer

Range("A1").Select
Cells.Find(What:="NEXT_HOP_IP = 172.17*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

newUpRow = ActiveCell.Row - 3
Rows(newUpRow).Delete shift:=xlUp

newDownRow = ActiveCell.Row + 3
Rows(newDownRow).Delete shift:=xlUp

End Sub

Scenario6 (not match my needs - but is close relative):

Sub DeleteSuccessfulRows()

Application.ScreenUpdating = False
Dim x
For x = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row To ActiveCell.Row Step -1
If Cells(x, 1) = "NEXT_HOP_IP = " Then 'If we find this text
Cells(x, 1).EntireRow.Delete 'Delete the entire row
Cells(x - 3, 1).EntireRow.Delete 'Delete the row 3 rows bellow it
Cells(x - 3, 1).EntireRow.Delete 'Delete the row 3 rows above it
x = x - 2
'Delete blank rows
ElseIf Cells(x, 1) = vbNullString Then Cells(x, 1).EntireRow.Delete
'Optional delete rows that contain "File looks like ..."
'ElseIf Cells(x, 1) = "File looks like it is not encrypted. Skipping ..." Then Cells(x, 1).EntireRow.Delete
'ElseIf Cells(x, 1) = "File could not be decrypted properly. Skipping ..." Then Cells(x, 1).EntireRow.Delete
End If
Next x
Application.ScreenUpdating = True

End Sub

------------


At your disposal,

Kind Regards,

Dimitris.
XLSM Data Entry Page.jpgXLSM LSP Creation Page 1 (OK).jpgXLSM LSP Creation Page 1 (Not-OK #2).jpg

Please read the forum rules at the top. Select "Forum Rules" and read the section about cross posting. This also goes for the other site(s) you posted at.

Originally Posted by jolivanes

Please read the forum rules at the top. Select "Forum Rules" and read the section about cross posting. This also goes for the other site(s) you posted at.

Hello, I was not aware about cross posting. What is next? Please let me know.

http://www.excelguru.ca/content.php?184