Finding value in Array

[Rasm]

I have an array of strings (the strings are not on any sheet - just in a dynamic array - the array is dimmed as say i.e. "Dim MyString(20,5) as string" - I would like the match to be exact - so If I search for "Test" - and I have a value "Test" & "Test123" - I would like to only get the Index for "Test" - Ideally I would like my index to be say 5,3 if Mystring(5,3)="Test".

 

[mikerickson]

Dim i As Long, j As Long

For i = LBound(MyString, 1) to UBound(MyString, 1)
    For j = LBound(MyString, 2) to UBound(MyString, 2)
        If myString(i, j) = "Test" Then
            MsgBox "Found in " & i & "," & j
        End If
    Next j
Next I

 

[MickG]

Try this:-

[COLOR=navy]Sub[/COLOR] MG19Jun54
[COLOR=navy]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Rw [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] MyString
MyString = Range("A1:E20")
[COLOR=navy]For[/COLOR] Ac = 1 To Ubound(MyString,2)
    [COLOR=navy]For[/COLOR] Rw = 1 To Ubound(MyString,1)
        [COLOR=navy]If[/COLOR] MyString(Rw, Ac) = "Test" [COLOR=navy]Then[/COLOR]
            MsgBox "MtString (" & Rw & "," & Ac & ") = Test"
            [COLOR=navy]Exit[/COLOR] [COLOR=navy]Sub[/COLOR]
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Rw
 [COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Rasm]

Thanks - I should have been more specific - my array is huge - so loops are too long.

Anyway I can use Match or some other worksheetfunction. What I am doing is using worksheetfunction.Large(nameOfArray,Rank) to find the rank in my array - so say I look for the highest 10 values - so the .large function returns the actual value - but not the index in my array - so now I want to find the index - but using loops takes to long.

 

[shg]

Can the sought value appear more than once in the array?

 

[mikerickson]

Is the array sorted in any fashion?

 

[shg]

I think Mike's nested loops will be much faster than trying to use Match(string, Index(array, 0, i), 0) in VBA.

 

[mikerickson]

I'm not clear about the array, the OP says an array of strings, yet #4 mentions using LARGE.

 

[Rasm]

It is actually a correlation matrix - so it is numbers - So I have say 1000 observations each with 100 numerical variables. So I use the worksheetfunction.RSQ to make an array of correlations^2 - next I use the worksheetfunction.large to find the highest RSQ - but I need to know the index for what .Large returns - so yes they are all numeric and yes the same number can appear twice. I should have been more precise early on - sorry.
But you think Mike's loop may be faster than .Match - hmmm - interesting.

 

[shg]

Sub Test()
    Const vWhat As Double = 1.234
    Const iLB1 As Long = -100
    Const iUB1 As Long = 200
    Const iLB2 As Long = 200
    Const iUB2 As Long = 500
    Dim av(iLB1 To iUB1, iLB2 To iUB2) As String
    
    Dim v As Variant
    
    Debug.Print Timer

    av(iUB1, iUB2) = vWhat
    v = FindVal1(vWhat, av)
    Debug.Print Timer, v(0), v(1), av(v(0), v(1))
    
    v = FindVal2(vWhat, av)
    Debug.Print Timer, v(0), v(1), av(v(0), v(1))
End Sub
 
Function FindVal1(sWhat As String, av As Variant) As Variant
    Dim iLB1        As Long
    Dim iUB1        As Long
    Dim iLB2        As Long
    Dim iUB2        As Long
    Dim i           As Long
    Dim v           As Variant
 
    iLB1 = LBound(av, 1)
    iUB1 = UBound(av, 1)
    iLB2 = LBound(av, 2)
    iUB2 = UBound(av, 2)
 
    With Application
        If iUB1 - iLB1 > iUB2 - iLB2 Then
            For i = iLB2 To iUB2
                v = .Match(sWhat, .Index(av, 0, i - iLB2 + 1), 0)
                If Not IsError(v) Then
                    FindVal1 = Array(CLng(v + iLB1 - 1), i)
                    Exit Function
                End If
            Next i
        Else
            For i = iLB1 To iUB1
                v = .Match(sWhat, .Index(av, i - iLB1 + 1, 0), 0)
                If Not IsError(v) Then
                    FindVal1 = Array(i, CLng(v + iLB2 - 1))
                    Exit Function
                End If
            Next i
        End If
    End With
End Function
 
Function FindVal2(sWhat As String, av As Variant) As Variant
    Dim i As Long
    Dim j As Long
    For i = LBound(av, 1) To UBound(av, 1)
        For j = LBound(av, 2) To UBound(av, 2)
            If av(i, j) = sWhat Then
                FindVal2 = Array(i, j)
            End If
        Next j
    Next i
End Function