searching through an array without looping through it.

[jakeb3482]

ok so i need to look through an array to see if a value is in there. i dont think i can loop through the array though with the current structure of the code. is there anyway to see if a string is in an array without looping through it? here is my current code:

device_array is the array i need to search through in the ElseIf statements.

For y = 1 To last_row
    For x = 1 To last_col
        
        
        If Workbooks(device_map).Sheets("sheet1").Cells(y, x) = "0" Then
           Workbooks(device_map).Sheets("sheet1").Cells(y, x) = ""
           Workbooks(device_map).Sheets("sheet2").Cells(y, x) = ""
        
        ElseIf Workbooks(device_map).Sheets("sheet1").Cells(y, x) = device_array(z) Then
            'find coordinate
            coordinate = (x - 1) & (y - 1)
            'find start of column coordinate
            coord_col = find_in_col((x - 1), 5)
            'use start of column coordinate to find row coordinate
            coord_row = find_in_col_start((y - 1), 6, coord_col)
            'find 1kHz value in csv file and put into sheet 1 of map
            Workbooks(device_map).Sheets("sheet1").Cells(y, x) = Cells(coord_row, "L")
            'find 2600Hz value in csv file and put into sheet 2 of map
            Workbooks(device_map).Sheets("sheet2").Cells(y, x) = Cells(coord_row, "Z")

        ElseIf Workbooks(device_map).Sheets("sheet1").Cells(y, x) <> "" And Workbooks(device_map).Sheets("sheet1").Cells(y, x) <> device_array(z) And Workbooks(device_map).Sheets("sheet1").Cells(y, x) <> "X" Then
                'if die wasnt tested put an x
                Workbooks(device_map).Sheets("sheet1").Cells(y, x) = "Z"
                Workbooks(device_map).Sheets("sheet2").Cells(y, x) = "Z"
                
        End If
        
        
    Next x
Next y

 

[Andrew Poulsom]

You could try the Match WorksheetFunction.

 

[jakeb3482]

but how do i put the range of the array in the match function? the match function wants a column range, not an array range...

 

[jakeb3482]

figured it out. i made a function. woo hooo