asked on

Search column to find string then copy string parts to new worksheet in different columns

I have a log that is written every time a transaction happens in the system.
I have just moved into a new role and am being asked to analyse these logs often for issues with counts.
each of the logs has a two columns

The log is captured in a worksheet which takes on the day as its name that is
Day07 for the seventh day of the month
Day08 for the eighth and so on

The first column is always a data column
The second column is always a string of data

There are only two columns in the source data.

The following is an example of the source data

7/07/2015 14:42:47 : B19 1 Rod(s) drawn/pushed successfully on system

I would like to be able to search column B for the following

B19

I would then like to perform the follow per message

When B19 is detected I would like to copy that cell to a new worksheet called Data in the following format
|7/07/2015| |14:42:47| |:| |B19| |1| |Rod(s) drawn/pushed successfully on system|
Date should go to column A for all messages
Time should go to column B for all messages
The colon should go to column C for all messages
The message ID should go to column D for all messages
The digit (1) should got to column E
All other data should go to Column F

Each message should be saved to a new row in the data sheet.

I had intended on doing a loop in vba to iterate through the rows but am having trouble with the search syntax and copy / paste functions.

Rgonzo1971

Hi,

pls try

Sub Macro()

For Each sh In ActiveWorkbook.Worksheets
    If sh.Name Like "Day*" Then
        For Each c In sh.Range(sh.Range("B1"), sh.Range("B" & Cells.Rows.Count).End(xlUp))
            If c.Value Like "*B19*" Then
                arrC = Split(c, " ")
                If Evaluate("=ISERROR('Data'!A1)") Then
                    Set newSh = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count))
                    newSh.Name = "Data"
                    newSh.Range("A:A").NumberFormat = "dd.mm.yy"
                    Rw = 1
                Else
                    Set newSh = ActiveWorkbook.Sheets("Data")
                    Rw = newSh.Range("A" & Cells.Rows.Count).End(xlUp).Row + 1
                End If
                newSh.Range("A" & Rw) = c.Offset(0, -1)
                newSh.Range("B" & Rw) = arrC(0)
                newSh.Range("C" & Rw) = arrC(1)
                newSh.Range("D" & Rw) = arrC(2)
                newSh.Range("E" & Rw) = arrC(3)
                newSh.Range("F" & Rw) = Mid(c, Len(arrC(0) & " " & arrC(1) & " " & arrC(2) & " " & arrC(3)) + 2)
            End If
                
        Next
    End If
Next
End Sub

Open in new window

Regards

Subodh Tiwari (Neeraj)

You have shown only one sample or source data which is this....
7/07/2015 14:42:47 : B19 1 Rod(s) drawn/pushed successfully on system

Show some more sample data to know how the data changes in the source file.
Or even it would be better if you upload a sample file with some sample data to adopt the correct approach to achieve the task.

IT SSM

ASKER

Hi please find attached sample file to show the scope of the data. For the rows I am looking for the only change will be the number of rod(s)
sample.xlsx

IT SSM

ASKER

Rgonzo1971 -- I have tried your code it seems to be processing the first couple of lines then rolling over the loop without processing the code. No Errors generated. thanks please see sample data
I should have added I am using excel 2013

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IT SSM

ASKER

This works perfectly thanks Rgonzo1971