Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Matching and exporting
Hi all,
i have 2 sheets in a workbook with similar fields, which i read in using vba, and try to match specific fields before throwing both sets of data to abother sheet. my problem is that the first sheet of data comes in a 1 line format, whilst the 2 sheet comes in multiple lines, can i have the first sheet matched line to match off against multiple lines in the 2nd sheet? Does anyone have any idea how i can change (also improve) what i have written below to achieve this. All the fields (cells) have same data except the price, which is summed in the 1st sheet but broken up in the 2nd. Sub Recon() Sheets("B Details").Select Cells.Find(What:="T.Date", _ After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:= _ xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) _ .Activate BeginRow = ActiveCell.Row + 1 Range("A65535").End(xlUp).Select EndRow = ActiveCell.Row For E = BeginRow To EndRow Step 1 Sheets("B Details").Select Cells(E, 1).Select TradeDate = Cells(E, 1).Value Price = Cells(E, 2).Value ContractDate = Cells(E, 4).Text Matcher = Left(Cells(E, 16), 7) Sheets("F Details").Select Cells.Find(What:="T. Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:= _ xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) _ .Activate BeginRow1 = ActiveCell.Row + 1 Range("A65535").End(xlUp).Select EndRow1 = ActiveCell.Row For F = BeginRow1 To EndRow1 Step 1 Sheets("F Details").Select Cells(F, 20).Select TradeDate_U = Cells(F, 1).Value Price_U = Cells(F, 2).Value ContractDate_U = Cells(F, 6).Text Matcher_U = Left(Cells(F, 16), 7) If TradeDate = TradeDate_U And ContractDate = ContractDate_U _ And Matcher < "Matched" And Matcher_U < "Matched" Then Sheets("B Details").Select Cells(E, 16).Value = "Matched B" & Counter Sheets("B&F Details").Select Cells(F, 16).Value = "Matched B" & Counter Sheets("Matched").Select Range("A65535").End(xlUp).Select EndRowMatch = ActiveCell.Row Cells(EndRowMatch + 1, 1).Select Cells(EndRowMatch + 1, 1).Value = TradeDate Cells(EndRowMatch + 1, 2).Value = ContractDate Cells(EndRowMatch + 1, 3).Value = Price Cells(EndRowMatch + 1, 4).Value = "Matched B" & Counter ActiveCell.EntireRow.Interior.ColorIndex = 38 Cells(EndRowMatch + 2, 1).Select Cells(EndRowMatch + 2, 1).Value = TradeDate_U Cells(EndRowMatch + 2, 2).Value = ContractDate_U Cells(EndRowMatch + 2, 3).Value = Price_U Cells(EndRowMatch + 2, 4).Value = "Matched B" & Counter ActiveCell.EntireRow.Interior.ColorIndex = 37 Counter = Counter + 1 Exit For Else End If Next F Next E End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting data | Excel Worksheet Functions | |||
Matching identical data using data only once in the matching proce | Excel Discussion (Misc queries) | |||
Exporting data (Data Table) to Excel | Excel Worksheet Functions | |||
Matching data and linking it to the matching cell | Links and Linking in Excel | |||
Exporting data | Excel Programming |