Filtering a value in array:

[Abhijeet R. Joshi]

Hi all,

After some reaserch I was able to build the below vba code using arrays I did test these on some sample data and then worked well but I made some small changes in the code now its not passing the if function correctly..

Scenario: I am trying to find a value from a range of filtered data in the "data" sheet Col.P against another array in the "Working" Sheet Col.H..and if this passes the if function and finds the value in the other array then correspnding row in Col.X on the data sheet should have a value as "Valid"..

I understand VBA and excel at an average level, but I am completely new to arrays..
Please help...Also if you could suggest some links for array learning in vba that would be great..

ORIGINAL CODE:
 
Sub test1()
    Dim ary1, ary2, e, temp As String, x
    ary1 = Range("A1:A" & Range("D" & Application.Rows.Count).End(xlUp).Row)
    ary2 = Range("D2:D3" & Range("D" & Application.Rows.Count).End(xlUp).Row)
    For i = 2 To Range("A" & Application.Rows.Count).End(xlUp).Row
'For Each e In ary1
        e = Cells(i, 1).Value
        x = Application.Match(e, ary2, 0)
        If Not IsError(x) Then Cells(i, 2).Value = "Valid"
    Next
End Sub

AMENDED CODE:
 
Sub test1()
    Dim ary1, ary2, e, temp As String, x
 
    Sheets("Working").Select
    ary2 = Range("H2:H" & Range("H" & Application.Rows.Count).End(xlUp).Row)
 
    Sheets("Data").Select
    ary1 = Range("P2:P" & Range("P" & Application.Rows.Count).End(xlUp).Row)
 
    For i = 2 To Range("P" & Application.Rows.Count).End(xlUp).Row
'For Each e In ary1
         e = Cells(i, 16).Value
        x = Application.Match(e, ary2, 0)
        If Not IsError(x) Then Cells(i, 24).Value = "Valid"
    Next
End Sub

 

[Abhijeet R. Joshi]

Here is the sample data that I have in working sheet "Known Share","Reconciliation"

Sample of data in the "Data" Sheet: "Known Share","Grapes","Reconciliation","Apple","Orange","Pears"

Thanks in advance for your time and help...

 

[Luke M]

I think this is what you are looking for:

Sub CheckRanges()
    Dim rng1 As Range, rng2 As Range
    Dim c As Range
    Dim e As Variant, x As Variant
    
 
    With Sheets("Working")
        Set rng1 = .Range("H2:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
    End With
    
    With Sheets("Data")
        Set rng2 = .Range("P2:P" & .Cells(.Rows.Count, "P").End(xlUp).Row)
    
        Application.ScreenUpdating = False
        For Each c In rng2
            e = c.Value
            x = Application.Match(e, rng1, 0)
            If Not IsError(x) Then .Cells(c.Row, "X").Value = "Valid"
        Next
        Application.ScreenUpdating = True
    End With
End Sub

 

[Marc L]

Hi Abhijeet R. Joshi,​

​

so much to say about your code but I just resume by​

it's useless to define array variables and not use them, except waste RAM resources ‼​

​

This is the array's way :​

Sub Test()
    With Sheets("Working")
        ARW = Application.Transpose(.Range("H2", .Cells(.Rows.Count, 8).End(xlUp)).Value)
    End With
 
    With Sheets("Data")
        AD1 = .Range("P2", .Cells(.Rows.Count, 16).End(xlUp)).Value
        ReDim AD2$(1 To UBound(AD1), 1 To 1)
 
        For R& = 1 To UBound(AD1)
            If Not IsError(Application.Match(AD1(R, 1), ARW, 0)) Then AD2(R, 1) = "Valid"
        Next
 
        .[X2].Resize(UBound(AD2)).Value = AD2
    End With
End Sub

Like it !​

​

Could be useless too to transform a Range into a Row to transform it back to a Range … ​

 

[Abhijeet R. Joshi]

Thanks Luke and Marc, for the replies..

@Marc: Your code somehow does not seem to work, however the one provided by Luke works great..

@Luke: Thanks for the code, works perfect..

 

[Abhijeet R. Joshi]

In case I have to check if the values are contained in a cell then where do I make the changes...

Example: I want to find if a cell contains "Aplha" and "Mike" in any element of the array. matching element would be "Aplha hit by Mike"..
Col.A = "Aplha hit by Mike"
Col.C = "Aplha"
Col.D = "Mike"
I understand that we need to use the wildcard (*) for this, but how and where?
I know this sounds like military intelligence, but this is the example that came to my mind...

 

[Marc L]

@Marc: Your code somehow does not seem to work,

My code, based upon your amended code, works like a charm on my side !​

​

If you're not squared with array variable, you should stay with the slower but easy classic cells method …​

 

[Luke M]

Hi Abhi,

This should help explain things. You should be able to add the appropriate check to your code based on this example.

'This line at top of module makes it so that
'all our text comparisons are case-insensitive
Option Compare Text
 
Sub ExampleInstr()
Dim str1 As String
Dim str2 As String
 
str1 = "Mike"
str2 = "Alpha hit Mike"
 
'The InStr function takes 3 arguements
'Starting postion to begin looking at, String to search
'and string to look for.
 
'It will return an integer equivalent to starting point of
'the search string if found, otherwise 0 or null (depending on conditions)
'See the VB help file on InStr for more detail
If InStr(1, str2, str1) > 0 Then
    MsgBox "The value exits!"
Else
    MsgBox "Not found..."
End If
 
End Sub