Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup to various workbooks | Excel Worksheet Functions | |||
Lookup multiple workbooks | Excel Worksheet Functions | |||
Lookup multiple workbooks | Excel Discussion (Misc queries) | |||
lookup a value from multiple columns of two workbooks | Excel Worksheet Functions | |||
Help with lookup reference to other workbooks | Excel Worksheet Functions |