Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort and Extract Macro
To all, I have a question as follows: I知 trying get a macro to correlate data that appears on 2 separate sheets and transfer the relevant data over. Sheet 1 has dozens of lines of data spread over several columns which is updated daily. Column A in sheet 1 contains a number. For easy I値l call it a P number. The same P number is also found in sheet 2 in column F . The P number in sheet 2 column F can sometimes have a letter before it and sometimes after but it is number that is the reference and is the same P number in sheet 1 column A. I need a macro to run daily when requested to take the P number in sheet 1 column A for each line and find the same P number (discounting any letters present in the cell) in sheet 2 column F. For each P number in sheet 1 column A it needs to remove data from sheet 2 column G to T and copy it into the end columns of sheet 1 on the rows of the relevant P number from sheet 2. For every P number in sheet 1 column A there may not be a P number in sheet 2 column F. I had a response back from a user which is as follows, but I cant get this to work: Sh1RowCount = 1 With Sheets("Sheet1") Do While .Range("A" & Sh1RowCount) < "" find_Num = .Range("A" & Sh1RowCount) With Sheets("Sheet2") Found = False Sh2RowCount = 1 Do While .Range("C" & Sh2RowCount) < "" OldNum = .Range("C" & Sh2RowCount) 'remove characters from number NewNum = "" Do While OldNum < "" If IsNumeric(Left(OldNum, 1)) Then NewNum = NewNum & Left(OldNum, 1) End If If Len(OldNum) 1 Then OldNum = Mid(OldNum, 2) Else OldNum = "" End If Loop NewNum = Val(NewNum) If find_Num = NewNum Then Found = True Exit Do End If Sh2RowCount = Sh2RowCount + 1 Loop End With LastCol = .Cells(Sh1RowCount, Columns.Count).End(xlToLeft).Column NewCol = LastCol + 1 If Found = False Then .Cells(Sh1RowCount, NewCol) = "No Data" Else Sheets("Sheet2").Range("G" & Sh2RowCount & ":T" & Sh2RowCount).Copy _ Destination:=.Cells(Sh1RowCount, NewCol) End If Sh1RowCount = Sh1RowCount + 1 Loop End With Does anyone have any ideas on how I can make this work? Or am I missing something very simple? Thanks in advance for your help, Regards Joseph Crabtree |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort and Extract Macro | Excel Programming | |||
Data Extract and Sort Macro Help | Excel Programming | |||
Data Extract and sort | Excel Programming | |||
extract data after sort | Excel Programming | |||
extract and sort dates | Excel Discussion (Misc queries) |