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