vba macro to parse column for value in cell and copy text from cell to different sheet
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
Who is Participating?
Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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
example.xlsm
Experts Exchange Solution brought to you by
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
From novice to tech pro — start learning today.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
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.