Search Array for Value

[In Distress]

Morning all (or afternoon, depending on where in the world you are)

I have a completely ignorant and probably basic problem that I desperately need answering.

Okay the story thus far is this. I have a public string variable called (PVS_TheSelectedLGAIs) that is set by another procedure in my VBA Project.

In another procedure I have created a 4 dimensional array (4 columns of data), that I set using the following:

Dim SearchArray() as variant

SearchArray = Range((FirstRowAddress),(LastRowAddress))

The Variables (FirstRowAddress) & (LastRowAddress)) are used earlier in my procedure to determine the range that I want use in my array. I believe this is the equivalent of Range("A1:D11350") - or there abouts.

Now, what I want to do is search for a value in column 1 of the newly created array. The value I want to try and find is the value in the (PVS_TheSelectedLGAIs) variable that I mentioned before. If it finds the value in the array, there are several things I want to do with it, but for now I just want to display the text "found value" in a message box, but if it doesn't find it, I want to display the text "Didn't find it" in a message box.

I can't for the life of me figure out how to do this.

I'm ashamed and should bow my head in shame for not knowing this, but I'm new to arrays in VBA, and as you can see I'm struggling.

Thanks in advance all. You'll be saving the few remaining hairs on my head that I have left.

 

[jindon]

Hi

First of all, your array is not 4dimentioned, but only 2 dimention

Assuming all your procedures working right..
try

Sub test()
Dim x, msg As String
With Application.WorksheetFunction
      On Error Resume Next
      x = .Match(PVS_TheSelectedLGAIs,.Index(SearchArray,0,1),0)
      If Err <> 0 Then
         msg = "Not Found"
      Else
         msg = "Found in " & x & "th row in the array"
      End If
      On Error GoTo 0
End With
      MsgBox msg
End Sub

 

[In Distress]

Hi

First of all, your array is not 4dimentioned, but only 2 dimention

Assuming all your procedures working right..
try

Sub test()
Dim x, msg As String
With Application.WorksheetFunction
      On Error Resume Next
      x = .Match(PVS_TheSelectedLGAIs,.Index(SearchArray,0,1),0)
      If Err <> 0 Then
         msg = "Not Found"
      Else
         msg = "Found in " & x & "th row in the array"
      End If
      On Error GoTo 0
End With
      MsgBox msg
End Sub

Cheers Jindon for the reply. Only problem is I'm getting a "Sub or function not defined" compile error on the word 'Match'.

Any ideas?

 

[jindon]

Hummm
Don't understand why you get that error..
how about this one?

Sub test()
Dim x, msg As String
With Application
      x = .Match(PVS_TheSelectedLGAIs,.Index(SearchArray,0,1),0)
      If IsError(x)
         msg = "Not Found"
      Else
         msg = "Found in " & x & "th row in the array"
      End If
End With
      MsgBox msg
End Sub

 

[In Distress]

It's all good jindon. I didn't see the period before the word Match on the screen - so naturally it wouldn't work.

Works like a treat. Thanks for the help.

Cheers