CODEWITHSUNDEEP
arraysvbaexcel
Ramzy Syed
Ramzy Syed

Reputation: 3

Check if a row contains a value from a pre-determined array in vba

I have a pre-determined array. Lets call it arr={"ok","okay","k"}
Now i have a worksheet with column headers as Fine, Great, okay, excellent
I want to find the column occurrence and select that column.
In this example, since "okay" matches, the answer should be 3 

    For i = 1 To Lastc
    if sh.Cells(1,i)=?array? then ColSel = i

Here, Lastc is the last column in the sheet
sh is the worksheet where I am checking
ColSel will store the index number of the column where it was a match in the array
?array? is not correct. I am not sure what to insert here

Upvotes: 0

Views: 1026

Answers (4)

Priyank
Priyank

Reputation: 1

Sub Test()

    arr = Array("A", "B", "C")

    For i = 1 To Lastc
    For j = 0 To UBound(arr)

    If Sh.Cells(1, i) = arr(j) Then
    ColSel = i
    Exit For
    End If
    Next
    Next

End Sub

Upvotes: 0

SJR
SJR

Reputation: 23081

Slight variation on other answers to search the array with one loop.

Sub x()

Dim arr, i As Long, j As Long, colsel As Long

arr = Array("ok", "okay", "k")

For i = 1 To 4
    If IsNumeric(Application.Match(Cells(1, i), Application.Index(arr, 1, 0), 0)) Then
        colsel = i
        Exit For
    End If
Next

End Sub

Upvotes: 0

bobajob
bobajob

Reputation: 1192

You could flip it round, and loop through the elements of arr instead:

For Each el In arr
    If Not IsError(Application.Match(el, Range(Cells(1, 1), Cells(1, LastC)), 0)) Then
        ColSel = Application.Match(el, Range(Cells(1, 1), Cells(1, LastC)), 0)
    End If
Next el

Upvotes: 1

Limak
Limak

Reputation: 1521

As far as I know, VBA does not have some function, that searches in array for a particular value. You can do it by yourself by looping through every value in your array. This solution works fine for me:

Sub test()
Dim arr(2) As String
Dim Lastc As Integer
arr(0) = "ok"
arr(1) = "okay"
arr(2) = "k"
Lastc = 4
Set sh = Sheets("Arkusz2")

For i = 1 To Lastc
    For j = 0 To UBound(arr)
        If sh.Cells(1, i) = arr(j) Then
            MsgBox "The answer is: " & i
            End
        End If
    Next j
Next i

End Sub

Upvotes: 0

Related Questions