+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    02-13-2021
    Location
    Athens, Greece
    MS-Off Ver
    Office 365 Professional Plus!
    Posts
    2

    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

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,772

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

    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.

  3. #3
    Registered User
    Join Date
    02-13-2021
    Location
    Athens, Greece
    MS-Off Ver
    Office 365 Professional Plus!
    Posts
    2

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

    Quote Originally Posted by jolivanes View Post
    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.

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,772

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Require help with to create formula 'to add to IPV4 address'
    By svimalan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-20-2021, 01:16 PM
  2. [SOLVED] VBA Code: Search column for text match, if found add text into row
    By markpem in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-18-2014, 08:00 AM
  3. VBA Code to Replace Cell Value when Address found by Index/Match
    By Rubert in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-23-2013, 10:27 AM
  4. VBA Code Help - If text found, send email to corresponding address.
    By spoliquin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-23-2013, 12:04 PM
  5. Hello...IPv4/IPv6 Address and Network Data
    By peterso8 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 05-21-2013, 12:13 PM
  6. [SOLVED] Locate column with specific title and delete rows if predefined set of text is found
    By johnny_tc in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-25-2012, 01:57 PM
  7. Find text String and select cell address where it is found?
    By JCIrish in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-16-2006, 09:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1