Find and copy problem

[utkarsh.majmudar]

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

 

[Dave Peterson]

You may not need code at all.

=vlookup() looks like it would be perfect.

=if(iserror(vlookup(a1,[book2.xls]sheet1!a:b,2,false)),"",
vlookup(a1,[book2.xls]sheet1!a:b,2,false))

(all one cell)

Debra Dalgleish's has some notes you may like:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

 

[utkarsh.majmudar]

Thanks Dave. Unfortunately, the data in second file is NOT sorted.
Hence I cannot use vlookup.

 

[Dave Peterson]

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.

 

[utkarsh.majmudar]

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]

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

 

[utkarsh.majmudar]

Thanks, Dave. This works perfectly. A definite learning for me.

 

[Dave Peterson]

Glad it worked ok.

Thanks, Dave. This works perfectly. A definite learning for me.