Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP
i have two sheets, one has a list of names and corresponding amounts.
the other sheet contains a second list of names and amounts in the next two columns. Some of the names are appearing in the first list. i need to create a macro which will scan through the two sheets, and if the same name is in the two list then i want those names to appear on the third sheet and in the next three columns i want the amounts from the first list and the amounts from the second and third columns of the second sheet. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP
The following assumes data starts in A2 on all sheets (there is a
header row). If this isn't true, you will need to modify the code a bit. Hth, merjet Sub CopyMatches() Dim c1 As Range Dim c2 As Range Dim c3 As Range Dim rng1 As Range Dim rng2 As Range Dim iRow As Long With Worksheets("Sheet1") Set rng1 = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)) End With With Worksheets("Sheet2") Set rng2 = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)) End With With Worksheets("Sheet3") .Range("A2:D30").Clear Set c3 = .Range("A2") End With For Each c1 In rng1 For Each c2 In rng2 If c1.Value = c2.Value Then iRow = iRow + 1 c1.Resize(1, 2).Copy Destination:=c3(iRow) c2.Offset(0, 1).Resize(1, 2).Copy Destination:=c3(iRow).Offset(0, 2) End If Next c2 Next c1 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming |