VBA equivalent for worksheet 'match'?

[George]

I want to copy a column to one of several other cols, based on matching
one cell in the first col to corresponding cells in the other cols. Eg:

A B C D E
-----------------------
1 b a b c d
2 1
3 2
4 3
5 4
6 5
7 6

A1 contains the index ('b'), and B1:E1 are the values to match that
against ('a .. d'). In this case, I'd want to copy col A to col C.

I can do this by using another cell (say A8) to determine an offset:

A8 = match(A8,B1:E1)

Together with the macro ...

Sub Macro1()
Range("A1:A7").Copy
Range("A1:A7").Offset("0", Range("A8").Value).PasteSpecial _
xlPasteValues
End Sub

Is there a way to do this strictly in VBA - ie, w/o the 'match' cell
(A8)?

Thanks,
George

 

[Vergel Adriano]

George,

One way:

Sub Macro1()
Dim iColOffset As Long
iColOffset = WorksheetFunction.Match(Range("A1"), Range("B1:E1"))
Range("A1:A7").Copy
Range("A1:A7").Offset(0, iColOffset).PasteSpecial xlPasteValues
End Sub

 

[George]

,,,,

Hope that helps.

Yes, thanks.

G