Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and copy problem
Hi
I have two files. The first file contains a list which is a subset of a list in another file. I need to pick values against the list in the second file and place it in the first file. First file A X D B Second file A 10 B 20 C 30 D 40 The First file on completion of the task should look like: A 10 X D 40 B 20 I am able to accomplish the task using the code below. The problem is I'm not sure how to do a error check. This leads to a output like: A 10 X 10 D 40 B 20 since the value 'X' does not exist in the second file. Would appreciate very much if someone could provide a fix. Thanks ********************** Sub demo() Dim i, Destinfile, Destinsheet, Destincolumn, wb1, wb2 Application.ScreenUpdating = False startrow = 2 Range("A65536").Select Selection.End(xlUp).Select lastrow = ActiveCell.Row Destinfile = InputBox("Please enter file name") Destinsheet = InputBox("Enter Sheet name") Destincolumn = InputBox("Enter column number for input") Set wb1 = ActiveWorkbook Set wb2 = Workbooks.Open(Destinfile) For i = startrow To lastrow inp = wb1.Sheets("Sheet1").Range("A" & i).Value wb2.Sheets(Destinsheet).Activate Cells.Find(What:=inp, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate On Error Resume Next rownum = ActiveCell.Row outp = ActiveCell.Offset(0, Destincolumn - 1) wb1.Sheets("Sheet1").Range("B" & i).Value = outp Next i wb2.Close False Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and copy problem
Thanks Dave. Unfortunately, the data in second file is NOT sorted.
Hence I cannot use vlookup. :( |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and copy problem
Are you looking for an exact match?
If yes, the data doesn't need to be sorted. The 4th parameter (false) must be specified, though. wrote: Thanks Dave. Unfortunately, the data in second file is NOT sorted. Hence I cannot use vlookup. :( -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and copy problem
I'll take a shot at vlookup again.
Actually the problem is a little complicated and hence my preference forcoding vs. vlookup. The second file is non-uniform in that the column from which the data to be picked up appears is not constant (hence the user input for column no.). Also different items are being picked up from different sheets (hence the user input for sheet name). I am sort of trying to create summary of a 10-K/10-Q financial statement available in an excel file. Each year/quarter the format of the file changes somewhat. I hope I am sounding clearer now. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and copy problem
I would still take the =vlookup() formula approach.
But you could plop in that formula in your code and take care of what sheet and what column when you build the formula. With not a lot of validation.... Option Explicit Sub demo2() Dim DestInFile As Variant Dim DestInColumn As Range Dim ws1 As Worksheet Dim wb2 As Workbook Dim LastRow As Long Dim LookUpRng As Range Application.ScreenUpdating = False Set ws1 = ActiveSheet With ws1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row DestInFile = Application.GetOpenFilename("Excel Files, *.xls") If DestInFile = False Then Exit Sub 'user hit cancel End If Set wb2 = Workbooks.Open(Filename:=DestInFile) Application.ScreenUpdating = True Set DestInColumn = Nothing On Error Resume Next Set DestInColumn _ = Application.InputBox _ (Prompt:="Please select a cell in the correct " & _ "worksheet that should be retrieved", Type:=8).Cells(1) On Error GoTo 0 Application.ScreenUpdating = False If DestInColumn Is Nothing Then 'user hit cancel wb2.Close savechanges:=False Exit Sub End If Set LookUpRng _ = DestInColumn.Parent.Range("a1") _ .Resize(, DestInColumn.Column).EntireColumn With .Range("b2:B" & LastRow) .Formula = "=vlookup(a2," & LookUpRng.Address(external:=True) _ & "," & DestInColumn.Column & ",false)" .Value = .Value .Replace what:="#N/A", replacement:="", _ lookat:=xlWhole, MatchCase:=False End With wb2.Close savechanges:=False End With Application.ScreenUpdating = True End Sub wrote: I'll take a shot at vlookup again. Actually the problem is a little complicated and hence my preference forcoding vs. vlookup. The second file is non-uniform in that the column from which the data to be picked up appears is not constant (hence the user input for column no.). Also different items are being picked up from different sheets (hence the user input for sheet name). I am sort of trying to create summary of a 10-K/10-Q financial statement available in an excel file. Each year/quarter the format of the file changes somewhat. I hope I am sounding clearer now. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and copy problem
Thanks, Dave. This works perfectly. A definite learning for me.
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and copy problem
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy contents of Find (Find and Replace) | Excel Discussion (Misc queries) | |||
Find in XML and Copy | Excel Discussion (Misc queries) | |||
Find and Copy | Excel Discussion (Misc queries) | |||
Find and Copy loop problem | Excel Discussion (Misc queries) | |||
Find a day and copy | Excel Programming |