macro and lookup in workbooks
 

Hi all:

I am hoping someone can help with this.

The summary of the code is: when the recon...workbook is open, it the
opens another workbook called ap... Then it looks at sheet1 of th
recon...workbook and looks for the item in column A2 (to the end o
column A) in column D of sheet2 of the AP...workbook. If it finds it
it then returns the item in column Q of sheet2 of the ap...workboo
into column B of sheet1 of recon....workbook.

Sheets 1 & 2 have headings in row 1.

How would I change this code such that if excel finds the value o
column A(sheet1 of the workbook recon...) in column D of sheet2 of th
ap....workbook. If this is the very first value found, I want excel t
copy the heading (1st row of sheet2 of ap...) and the whole row i
finds (i.e. sheet 2 of ap...) and pastes it in sheet3 of the workboo
ap (this way sheet 3 has a heading).....If it's not the first value, i
should paste only the entire row, in the LAST row of sheet3 of ap....

It sounds complicated, but I think it's executable.




Sub OpenAP()
ChDir "C:\Documents and Settings\xxx\Desktop\Costing"
Workbooks.Open Filename:= _
"C:\Documents and Settings\xxx\Desktop\Costing\AP040604.xls"
Workbooks("reconciliation040604.xls").Activate
End Sub

Sub Searching()
Application.ScreenUpdating = False
OpenAP
Workbooks("reconciliation040604.xls").Activate
Sheets(1).Activate
'Assuming, that the first name is in the range A2 and the name
follows
continously after each other
Range("A2").Select 'set the begin range as your choice

While ActiveCell < ""
Application.StatusBar = ActiveCell.Row & " " & ActiveCell
SName = ActiveCell
SMatch = FindingName(SName)
If SMatch < "" Then
ActiveCell.Offset(0, 1) = SMatch
End If
ActiveCell.Offset(1, 0).Select
Wend
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub


Function FindingName(srch) As String
Set c
Workbooks("AP040604.xls").Sheets(2).Columns("D").F ind(srch,
LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
FindingName = c.Offset(0, 13) 'Value of column Q in th
same row
Else
FindingName = ""
End If
End Functio

--
Message posted from http://www.ExcelForum.com