Q:

Use VBA to insert values into variant based on matching value

Say I'm using VBA and have value stored as a string.  I want to compare this string to a series of values on a sheet, and then insert the row values where there is a match into a variant.  For example:
MyValue = "TestMe"

Sheet("Test")
name   val1   val2   val3   val4
qqq    2       3    yes      a
aaa    5       6     no      d
TestMe 4       7    yes      z

I want the values for the row "TestMe" to be extracted into a variant.  What's the most efficient way to do this?  I have a way but it seems slow and I'm curious if there is a better way.  Right now I'm looping through the range on Sheet("Test").

A:

I would think of something like this:
Dim x As String
Dim r As Range
Dim values

x = "TestMe"
Set r = Me.Columns("A").Find(what:=x, lookat:=xlWhole, MatchCase:=True)
If Not r Is Nothing Then
    values = Application.Intersect(Me.UsedRange, r.EntireRow).Value
End If

values will be a 1-based 2-dimensional array (even if it's just from one row or column).
(E. g. values(1, 3) would be 7 in this sample.)

