Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA equivalent for worksheet 'match'?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA equivalent for worksheet 'match'?
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 -- Hope that helps. Vergel Adriano "George" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA equivalent for worksheet 'match'?
On Fri, 20 Apr 2007 05:36:04 -0700, Vergel Adriano
wrote: ,,,, Hope that helps. Yes, thanks. G |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
match two cells from one worksheet to another | Excel Worksheet Functions | |||
Match 2 worksheet | Excel Worksheet Functions | |||
Match and merge on new worksheet | Excel Worksheet Functions | |||
using index, match on another worksheet | Excel Worksheet Functions | |||
Match a value from a column in worksheet | Excel Worksheet Functions |