Search Array for Value

In Distress

New Member
Joined
Mar 12, 2003
Messages
37
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

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

Assuming all your procedures working right..
try
Code:
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

New Member
Joined
Mar 12, 2003
Messages
37
Hi

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

Assuming all your procedures working right..
try
Code:
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hummm
Don't understand why you get that error..
how about this one?
Code:
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

New Member
Joined
Mar 12, 2003
Messages
37
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
 

Forum statistics

Threads
1,141,095
Messages
5,704,310
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top