Consulting

Results 1 to 3 of 3

Thread: VBA Script seems working at first glance, but does not..

  1. #1
    VBAX Newbie
    Joined
    Jul 2018
    Posts
    2
    Location

    Question VBA Script seems working at first glance, but does not..

    Hi all,

    I have the below VBA script that should apply a filter on my worksheet, and at first it seems to be working fine, but when doind a little double check the file after the filter has been applied contains additional 2 values from column C, namely "98631011" and "87108890" that are not in the search criteria .. Can anyone help me figure out how to fix this so that the filter actualy filter only the correct criterias and not additional?


    vbatestfile.xlsx


    Sub ShowMatches()
         Dim r As Long
         Dim LastRow As Long
         Dim SearchCriteria As String
         Dim arr() As String
         Dim i As Long
         Dim HideRow As Boolean
    
         SearchCriteria = ("1433;56827013;40632454;1007;47312665;1348;86602530;21941628;30605726;33150026;33790010;88826677;56631022;88383800;23706949;35356287;32545500;33364040;33179800;26174532;46368668;20329969;28820430;39692100;40885225;70151400;21222364;86276500;86103700;32180385;38696688;86127912;42404766;25648828;22732176;87430238;77666001;55868182;33110775;21439583;1029;3545621826;33696007;35813500;86175455;33480500;49131449;69120717;33181030;33305522;33161672;1046;1011;33113311;33470707;1088;33382888;1049;60161214;23256057;86117794;40748780;30710003;1418;1417;58263200;58200115;25858987;21230742;86127916;1390;21842176;1446")
         If SearchCriteria = "" Then Exit Sub
         Application.ScreenUpdating = False
         arr = Split(SearchCriteria, ";")
         LastRow = [D65536].End(xlUp).Row + 1
         For r = LastRow To 2 Step -1
              For i = LBound(arr) To UBound(arr)
                   If InStr(1, Cells(r, "C"), arr(i)) > 0 Then
                        HideRow = False
                   End If
              Next
              Rows(r).Hidden = HideRow
              HideRow = True
         Next
         Application.ScreenUpdating = True
    End Sub
    Best Regards
    Stig

  2. #2
    This is because you are using the Instr function which will return 5 for the item "1011" in string "98631011". You must add a test for the length of both strings if you want to test for an exact match or simply use:

    If CStr(Cells(r, "C")) = arr(i) Then
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    VBAX Newbie
    Joined
    Jul 2018
    Posts
    2
    Location
    Hi Jan

    Thank you so much .. it seems to have helped .. I just need to do some more testing, but at first run it looks perfect :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •