Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Unable to get the Search property of the WorksheetFunction Class

Posted on 2014-02-12
3
Medium Priority
?
1,622 Views
Last Modified: 2014-02-13
Hello Experts,

I have a macro kindly written by byundt which is designed to look at a column E which contains a message.  The macro would then search through the message cell and extract each of the lines and then put them into columns under the correct headings.

When I now run the macro I keep getting the "Unable to get the Search property of the WorksheetFunction Class".  I have worked out that it is caused by running into a blank cell.  

Could anyone help me configure the macro to move on if encountering any blank cells?  I have tried to figure out a way myself but just end up breaking the macro completely.

I have attached an example of the macro and data for your review.

Regards,
InfoSec36
WorksheetFunction-Class-error.xls
0
Comment
Question by:Sonia Bowditch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 19

Assisted Solution

by:Ken Butters
Ken Butters earned 400 total points
ID: 39854089
created new variable to know if we had empty cell
Dim EmptyCell As Boolean

Open in new window

added the following code around the worksheet function (in 2 places)
                    On Error Resume Next
                    j = Application.WorksheetFunction.Search("detailed authentication information:", s, i)
                    If Err = 0 Then
                        emtpyCell = False
                    Else
                        EmptyCell = True
                    End If
                    On Error GoTo 0

Open in new window

then modified this line to check for empty cell:
If Not EmptyCell Then: targ.Cells(n, k).Value = Trim(Mid$(s, i, j - i))

Open in new window


Attached update file
WorksheetFunction-Class-error.xls
0
 
LVL 81

Accepted Solution

by:
byundt earned 1600 total points
ID: 39854203
If you use Application.Search (instead of Application.WorksheetFunction.Search) and return the result to a Variant, it won't matter if SEARCH returns an error value. Nor will you need to turn error handling off.
Sub ParseAD_Report()
Dim cel As Range, rg As Range, targ As Range
Dim vHeaders As Variant
Dim s As String, s2 As String
Dim k As Long, n As Long, nHeaders As Long
Dim i As Variant, j As Variant
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
                i = Application.Search(vHeaders(1, k), s, j) + Len(vHeaders(1, k))
                If Not IsError(i) Then
                    If k = nHeaders Then
                        j = Application.Search("detailed authentication information:", s, i)
                    Else
                        s2 = vHeaders(1, k + 1)
                        If s2 = "" Then
                            s2 = vHeaders(1, k + 2)
                        End If
                        j = Application.Search(s2, s, i)
                    End If
                End If
                If (Not IsError(i)) And (Not IsError(j)) Then targ.Cells(n, k).Value = Trim(Mid$(s, i, j - i))
            End If
        Next
    End If
Next
End Sub

Open in new window

0
 

Author Closing Comment

by:Sonia Bowditch
ID: 39855634
Thank you both very much for your help.

Byndt, works perfectly as usual.  Thank you.

Ken,  I will be able to utilise your solution in my other analysis worksheet.

Regards,
InfoSec36
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

722 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