error: "unable to get the search property of the worksheet function class"

J

johnmerlino

Board Regular
Joined
Sep 21, 2010
Messages
94
Hey all, I get this error:

"unable to get the search property of the worksheet function class"

when running this macro:

Code: 
Sub findDefendant()

Dim RENums As Object
Dim LValue  As String



Set RENums = CreateObject("VBScript.RegExp")


RENums.Pattern = "DEFENDANT"


  Dim lngLastRow As Long
  lngLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
   

  Dim i

  For i = 1 To lngLastRow

    If RENums.Test(Range("J" & i).Value) Then
    
        LValue = Range("K" & i)

        LValue2 = Right(Range("G" & i), Len(Range("G" & i)) - WorksheetFunction.Search("V", Range("G" & i)))

    
        Range("N" & i).Value = LValue
        Range("M" & i).Value = LValue2
        
    
        
    End If

  Next i

End Sub
It occurs when I try to extract all the content after the character V in cell G and place it in cell m.

With this data:

[TABLE="width: 976"]
<colgroup><col style="width: 48pt;" span="12" width="64"> <col style="width: 156pt;" width="208"> </colgroup><tbody>[TR]
[TD="width: 64"]2011CA002611[/TD]
[TD="class: xl63, width: 64, align: center"]########[/TD]
[TD="class: xl63, width: 64, align: center"]########[/TD]
[TD="width: 64"]AW[/TD]
[TD="width: 64"]MAIN BRANCH[/TD]
[TD="width: 64"]HR >$50K, <$250K[/TD]
[TD="width: 192, colspan: 3"]blah vs blah
[/TD]
[TD="width: 64"]JUDGE[/TD]
[TD="width: 64"]HOY, JUDGE JOHN[/TD]
[TD="width: 64"],[/TD]
[TD="width: 208"],[/TD]
[/TR]
[TR]
[TD]2011SC001606[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]RD[/TD]
[TD]SOUTH BRANCH[/TD]
[TD]SMALL CLAIMS >$2500[/TD]
[TD="colspan: 2"]blah vs blah
[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]SERVICE PARTY[/TD]
[TD]HERNANDES, FERNANDO[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
[TR]
[TD]2011CA002595[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]AH[/TD]
[TD]MAIN BRANCH[/TD]
[TD]CONTRACT & DEBT[/TD]
[TD="colspan: 3"]blah vs blah
[/TD]
[TD]JUDGE[/TD]
[TD]BROWN, JUDGE LUCY[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
[TR]
[TD]2011CC002687[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]RH[/TD]
[TD]NORTH BRANCH[/TD]
[TD]EVICTION (COUNTY CIVIL)[/TD]
[TD="colspan: 3"]blah vs blah
[/TD]
[TD]DEFENDANT[/TD]
[TD]FULLWOOD, ANTHONY[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
[TR]
[TD]2011CA002586[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]AJ[/TD]
[TD]MAIN BRANCH[/TD]
[TD]AUTO NEGLIGENCE[/TD]
[TD="colspan: 3"]blah vs blah
[/TD]
[TD]DEFENDANT[/TD]
[TD]STATE FARM MUTUAL AUTOMOBILE INSURANCE COMPANY,[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
[TR]
[TD]2011CA002586[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]AJ[/TD]
[TD]MAIN BRANCH[/TD]
[TD]AUTO NEGLIGENCE[/TD]
[TD="colspan: 3"]blah vs blah
[/TD]
[TD]JUDGE[/TD]
[TD]ROSENBERG, JUDGE ROBIN[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
[TR]
[TD]2011CA002586[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]AJ[/TD]
[TD]MAIN BRANCH[/TD]
[TD]AUTO NEGLIGENCE[/TD]
[TD="colspan: 3"]blah vs blah
[/TD]
[TD]DEFENDANT[/TD]
[TD]WOODWARD, DURBIN[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
[TR]
[TD]2011CA002616[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]AV[/TD]
[TD]MAIN BRANCH[/TD]
[TD]HR FORECLOSURE >$50K, <$250K[/TD]
[TD="colspan: 3"]blah vs blah
[/TD]
[TD]DEFENDANT[/TD]
[TD]PRESERVE AT BOYNTON BEACH MAINTENANCE ASSOCIATION INC,[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
[TR]
[TD]2011CC002695[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]RH[/TD]
[TD]NORTH BRANCH[/TD]
[TD]EVICTION (COUNTY CIVIL)[/TD]
[TD="colspan: 3"]blah vs blah
[/TD]
[TD]DEFENDANT[/TD]
[TD]CAROL DAWSON N/K/A CAROL SMITH,[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
[TR]
[TD]2011CA002616[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]AV[/TD]
[TD]MAIN BRANCH[/TD]
[TD]HR FORECLOSURE >$50K, <$250K[/TD]
[TD="colspan: 3"]blah vs blah
[/TD]
[TD]DEFENDANT[/TD]
[TD]PRESERVE AT BOYNTON BEACH 12 CONDOMINIUM ASSOCIATION INC,[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
[TR]
[TD]2011CC002658[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]RE[/TD]
[TD]MAIN BRANCH[/TD]
[TD]EVICTION (COUNTY CIVIL)[/TD]
[TD="colspan: 2"]blah vs blah
[/TD]
[TD][/TD]
[TD]DEFENDANT[/TD]
[TD]HILL, DEBORAH[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
[TR]
[TD]2011CC002686[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]RL[/TD]
[TD]MAIN BRANCH[/TD]
[TD]EVICTION (COUNTY CIVIL)[/TD]
[TD="colspan: 3"]blah vs blah
[/TD]
[TD]DEFENDANT[/TD]
[TD]WILLIAMS, GENESIA[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
[TR]
[TD]2011CA002587[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]AV[/TD]
[TD]MAIN BRANCH[/TD]
[TD]HR FORECLOSURE => $250K[/TD]
[TD="colspan: 3"]blah vs blah
[/TD]
[TD]DEFENDANT[/TD]
[TD]ANDERSON, OSWALD[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
[TR]
[TD]2011CC002674[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]RJ[/TD]
[TD]MAIN BRANCH[/TD]
[TD]EVICTION (COUNTY CIVIL)[/TD]
[TD="colspan: 3"]blah vs blah
[/TD]
[TD]DEFENDANT[/TD]
[TD]TILLMAN, JACOLBY[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
[TR]
[TD]2011CA002587[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD="class: xl63, align: center"]########[/TD]
[TD]AV[/TD]
[TD]MAIN BRANCH[/TD]
[TD]HR FORECLOSURE => $250K
[/TD]
[TD="colspan: 3"]blah vs blah
[/TD]
[TD]DEFENDANT[/TD]
[TD]MORTGAGE ELECTRONIC REGISTRATION SYSTEMS INC,[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited by a moderator:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Why not try using the VBA function InStr to find what you are looking for.
Code: 
pos = InStr("V", Range("G" & i))
If V isn't found this will return 0 rather than an error, so you can check for that before proceeding with the next part.

Something like this perhaps.
Code: 
If pos<>0 Then ' if V found continue
       LValue2 = Right(Range("G" & i), Len(Range("G" & i)) - pos)
End If
By the way the InStr might be the other way round, with the string to look in as the first argument and what to look for as the second.

I can never remember which way they go round.:)
 
Upvote 0
Thanks for response but your recommended code:

Code: 
Sub findDefendant()

Dim RENums As Object
Dim LValue  As String



Set RENums = CreateObject("VBScript.RegExp")


RENums.Pattern = "DEFENDANT"


  Dim lngLastRow As Long
  lngLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
   

  Dim i

  For i = 1 To lngLastRow

    If RENums.Test(Range("J" & i).Value) Then
    
        pos = InStr("V", Range("G" & i))
    
        LValue = Range("K" & i)

        If pos <> 0 Then 'if V found continue
           LValue2 = Right(Range("G" & i), Len(Range("G" & i)) - pos)
        End If

    
        Range("N" & i).Value = LValue
        Range("O" & i).Value = LValue2
        
    
        
    End If

  Next i

End Sub
does not put the values right of "V" from G into cell O. In fact, even though cell G does contain "V" in it, it leaves cell O completely blank for all instances.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,609
Messages
6,179,874
Members
452,949
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top