Solved

Searching Worksheet for Certain Data

Posted on 2014-02-18
6
252 Views
Last Modified: 2014-02-19
Hello Experts,

I have a brilliant macro provided by byundt to search through the message column (E) in one worksheet and extract data based on the headings in another worksheet.  The data is then copied into the worksheet under the correct headings.

I have run into a problem with using this macro on another worksheet.  It works perfectly if the format of the data in column E remains static.  Unfortunately, the data I have can give at least two different messages:

Cell 1 can give -   Account Name:      Supplied Realm Name:      User ID:      Service Name:      Service ID:      Client Address:      Client Port:      Ticket Options:      Result Code:      Ticket Encryption Type:

Cell 2 can give - Authentication Package:      Logon Account:      Source Workstation:      Error Code:

I would like to amend the macro to taking into account the different format of messages which can be found in column E.

I would be grateful for any help.
Regards,
InfoSec36
Mixed-Data.xls
0
Comment
Question by:infosec36
6 Comments
 
LVL 2

Assisted Solution

by:c_kedar
c_kedar earned 200 total points
ID: 39868508
Sub ParseColE()
    Dim ws2 As Worksheet
    Set ws2 = Worksheets("Formatted2")
    Dim rg As Range
    With Worksheets("Formatted")
        Set rg = .Range("E2")
        Set rg = .Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))
    End With
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "(.*:)(.*)\n"
    re.Global = True
    Dim var As Variant
    For Each cel In rg.Cells
        Set mm = re.Execute(cel.Text)
        For Each m In mm
            k = m.SubMatches(0)
            v = m.SubMatches(1)
            var = Application.Match(k, ws2.Rows(1), 0)
            If Not IsError(var) Then
                ws2.Cells(cel.Row, var) = v
            End If
        Next
    Next
End Sub

Open in new window

0
 
LVL 81

Accepted Solution

by:
byundt earned 300 total points
ID: 39868624
I shortened the code to look for the header labels and enter a value if one exists. If not, then continue to the next label.
Sub ParseAD_Report()
Dim cel As Range, rg As Range, targ As Range
Dim v As Variant, vHeaders As Variant
Dim s As String
Dim i As Long, j As Long, k As Long, n As Long, nHeaders As Long
Application.ScreenUpdating = False
With Worksheets("Formatted")
    Set rg = .Range("E2")       'First cell with data
    Set rg = .Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))
End With
With Worksheets("Formatted2")
    Set targ = .Range("A1")     'First header label
    Set targ = .Range(targ, .Cells(1, .Columns.Count).End(xlToLeft))    'All the header labels
    vHeaders = targ.Value
    nHeaders = targ.Columns.Count
    n = .UsedRange.Rows.Count
End With
For Each cel In rg.Cells
    s = cel.Value
    If s <> "" Then
        n = n + 1
        j = 1
        For k = 1 To nHeaders
            If vHeaders(1, k) <> "" Then
                v = Application.Search(vHeaders(1, k), s, 1)
                If Not IsError(v) Then
                    i = v + Len(vHeaders(1, k))
                    j = Application.Search(vbLf, s & vbLf, i)
                    targ.Cells(n, k).Value = Trim(Mid$(s, i, j - i))
                End If
            End If
        Next
    End If
Next
End Sub

Open in new window

Mixed-DataQ28367754.xls
0
 
LVL 81

Expert Comment

by:byundt
ID: 39868649
When testing c_kedar's code, change statement 14 to:
        Set mm = re.Execute(cel.Text & vbLf)

Open in new window


Without that tweak, it wasn't picking up the last field in each cell.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 22

Expert Comment

by:Dreamboat
ID: 39869253
Yeah. He is a bit brilliant, isn't he? Hey Brad! :)
0
 
LVL 81

Expert Comment

by:byundt
ID: 39869283
Long time no see, Anne.
0
 

Author Closing Comment

by:infosec36
ID: 39869704
Thanks  byundt.  The modification to the macro you had already designed worked brilliantly as usual :)  

c_kedar - thanks very much for your code.  It also works brilliantly.

Regards,
InforSec36
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Match formula returns N/A 5 25
Hlookup formula help 14 19
Highlighting cells in Excel 9 16
FormulaArray VBA Issue 6 17
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now