CODEWITHSUNDEEP
vbaexcel
Jeffrey Kramer
Jeffrey Kramer

Reputation: 1345

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").

Upvotes: 1

Views: 710

Answers (1)

KekuSemau
KekuSemau

Reputation: 6856

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.)

Upvotes: 1

Related Questions