vba macro to parse column for value in cell and copy text from cell to different sheet

romlig
romlig used Ask the Experts™
on
Hello Experts,

I need to parse each row in column A of the sheet "Data" and look for the text "ip route"
if the cell contains the text, I want to further check and see if the gateway IP address (the last IP in the cell) is equal to the IP address from cell B1 of the "Inputs" sheet.
if it does contain the same gateway IP, i want to create a new sheet called "output", copy the entire cell twice to the new sheet and prepend the text "no" to the first copy, and replace the gateway IP address (last IP in the cell) with the address contained in cell b4 of the input sheet in the second copy.  Please see attached spreadsheet for example data and example output  

Thanks
example.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
The description is a bit confusing at least to me.
Please upload the sample workbook and on output sheet, explain the output for at least first four rows and describe that each row belongs to which row on Data sheet and what portion of the IP is being changed and with what? As I don't find similarities or a pattern on the output sheet.
Sorry if I am missing the logic.
Commented:
Try the below code,

Sub Macro1()
'
' Macro1 Macro
'
    Dim sheetCreated As Boolean
    sheetCreated = False
    Dim outputRow As Integer
    outputRow = 1
    
    If SheetExists("Output") Then
        MsgBox ("'Output' sheet already exists. Remove 'Output' sheet and try again")
    Else
        For Idx = 1 To Worksheets("Data").Range("A1", Worksheets("Data").Range("A1").End(xlDown)).Count Step 1
            Set c = Worksheets("Data").Cells(Idx, 1)
            If InStr(1, c, "ip route") > 0 Then
                ' Split route expression, to get the gateway IP
                Words = Split(c, " ")
                
                ' If gateway IP matches with B1
                If Worksheets("Inputs").Range("B1").Value = Words(4) Then
                    If sheetCreated = False Then
                        sheetCreated = True
                        ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
                        Worksheets(Worksheets.Count).Name = "Output"
                    End If
                    ' copy the cell and prepend 'no'
                    Worksheets("Output").Cells(outputRow, 1) = "no " & c
                    outputRow = outputRow + 1
                    ' copy the cell and replace gateway IP
                    Worksheets("Output").Cells(outputRow, 1) = Words(0) & " " & Words(1) & " " & Words(2) & " " & Words(3) & " " & Worksheets("Inputs").Range("B4").Value
                    outputRow = outputRow + 1
                End If
            End If
        Next
    End If
'
End Sub

Function SheetExists(SheetName As String, Optional wb As Excel.Workbook)
   Dim s As Excel.Worksheet
   If wb Is Nothing Then Set wb = ThisWorkbook
   On Error Resume Next
   Set s = wb.Sheets(SheetName)
   On Error GoTo 0
   SheetExists = Not s Is Nothing
End Function

Open in new window


example.xlsm
romlig

Author

Commented:
Does exactly what I need, Thanks Hammadh

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial