Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Macro
Hello, could some one please help me with the following?
I have two Excel 2003 workbooks. One workbook is named: Master.xls and the worksheet is also named: Master. The other workbook is named: Stock Numbers.xls and the worksheet is also named: Stock Numbers. In column A of both the Master worksheet and the Stock Numbers worksheet is a list of Part Numbers. The Part Numbers start from A2:A65000 in each worksheet. In column J of the Stock Numbers worksheet there is a list of stock codes. In column J of the Master worksheet is a blank list of Stock Codes that needs to be populated with all corresponding (part number / stock code) matches found in the Stock Codes worksheet. I wish to get Excel to lookup each Part Number in Column A of the Master worksheet and see if there is a corresponding Part Numbers in column A of the Stock Numbers worksheet. If there is a match, then the corresponding Stock Number against the matched part Number in the Stock Numbers worksheet needs to be populated in column J of the Master worksheet against the matched part number. Example: Master worksheet: Columns A & J: Cell: A2: 001P87 Cell J2: "blank cell" A3: A28H96 Cell J3: "blank Cell" A4: X8500L cell J4: "blank cell" Stock Code worksheet: Columns A & J: Cell: A2: 001P87 Cell J2: 018519937 A3: BR54M Cell J3: 000012255 A4: X8500L Cell J4: 668510039 So, in the above example, when Excel looks up Column A of the Master worksheet, the part numbers: 001P87 & X8500L will find a corresponding match to those part numbers located in Column A of the Stock Codes worksheet. Thus, the stock codes in Column J of the Stock Codes worksheet need to be copied in to the corresponding blank cells in Column J of the Master worksheet. Then the worksheets should look like this after the stock codes have been copied across: Master worksheet: Columns A & J: Cell: A2: 001P87 Cell J2: 018519937 A3: A28H96 Cell J3: "blank Cell" A4: X8500L cell J4: 668510039 Stock Code worksheet: Column A & J: Cell: A2: 001P87 Cell J2: 018519937 A3: BR54M Cell J3: 000012255 A4: X8500L Cell J4: 668510039 If any one can help with creating a macro, it would be greatly appreciated. Thanks, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Macro
Hi, if you want a macro to run when you request it repeatedly then I can not
help. The standard function you want to use is a vlookup but I do not know how to write them in code. If a single vlookup will work then try entering this in column j on your master worksheet =VLOOKUP($A:$A,Sheet1!A:J,10,FALSE) to translate $A:$A is column A on your master worksheet, Sheet1!A:J is column a to j on your stock numbers worksheet. If you change these it should work for you. "Chris" wrote: Hello, could some one please help me with the following? I have two Excel 2003 workbooks. One workbook is named: Master.xls and the worksheet is also named: Master. The other workbook is named: Stock Numbers.xls and the worksheet is also named: Stock Numbers. In column A of both the Master worksheet and the Stock Numbers worksheet is a list of Part Numbers. The Part Numbers start from A2:A65000 in each worksheet. In column J of the Stock Numbers worksheet there is a list of stock codes. In column J of the Master worksheet is a blank list of Stock Codes that needs to be populated with all corresponding (part number / stock code) matches found in the Stock Codes worksheet. I wish to get Excel to lookup each Part Number in Column A of the Master worksheet and see if there is a corresponding Part Numbers in column A of the Stock Numbers worksheet. If there is a match, then the corresponding Stock Number against the matched part Number in the Stock Numbers worksheet needs to be populated in column J of the Master worksheet against the matched part number. Example: Master worksheet: Columns A & J: Cell: A2: 001P87 Cell J2: "blank cell" A3: A28H96 Cell J3: "blank Cell" A4: X8500L cell J4: "blank cell" Stock Code worksheet: Columns A & J: Cell: A2: 001P87 Cell J2: 018519937 A3: BR54M Cell J3: 000012255 A4: X8500L Cell J4: 668510039 So, in the above example, when Excel looks up Column A of the Master worksheet, the part numbers: 001P87 & X8500L will find a corresponding match to those part numbers located in Column A of the Stock Codes worksheet. Thus, the stock codes in Column J of the Stock Codes worksheet need to be copied in to the corresponding blank cells in Column J of the Master worksheet. Then the worksheets should look like this after the stock codes have been copied across: Master worksheet: Columns A & J: Cell: A2: 001P87 Cell J2: 018519937 A3: A28H96 Cell J3: "blank Cell" A4: X8500L cell J4: 668510039 Stock Code worksheet: Column A & J: Cell: A2: 001P87 Cell J2: 018519937 A3: BR54M Cell J3: 000012255 A4: X8500L Cell J4: 668510039 If any one can help with creating a macro, it would be greatly appreciated. Thanks, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Macro
Thanks Leanne for your reply - much appreciated. I need the vlookup
function to repeatly loop through all the thousands of part numbers automatically whilst the macro is running until the last part number has been through the loop. Kind regards, Chris *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Macro
If this formula is in the cell in each row it will work through all your part
numbers but as mentioned earlier I have not been able to crack the code for this in VBA. Sorry I can't be of more help - I am fairly new at VBA code but do know my way around functions and formulas. "Chris" wrote: Thanks Leanne for your reply - much appreciated. I need the vlookup function to repeatly loop through all the thousands of part numbers automatically whilst the macro is running until the last part number has been through the loop. Kind regards, Chris *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Macro
If I were doing this, I would put the formula in the master worksheet in column
J and drag it down as far as required. But with 65000 rows receiving the formula and a 65000 row table, this could take a longgggggggggggggggg time to recalculate (if it finishes at all!!!). If I needed a macro to do this, then I'd use a macro that would populate column J with those formulas. After the formulas recalculated, you could convert the formulas to values. I'd try this against smaller versions of the files (10k rows in both). Both files have to be open before you run the macro: Option Explicit Sub Testme() Dim MstrWks As Worksheet Dim StockNumWks As Worksheet Dim FormRng As Range Dim VLookUpAddr As String Dim LastRow As Long Set MstrWks = Workbooks("master.xls").Worksheets("master") Set StockNumWks = Workbooks("stock numbers.xls").Worksheets("Stock Numbers") With MstrWks LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set FormRng = .Range("J2:J" & LastRow) End With VLookUpAddr = StockNumWks.Range("a:J").Address(external:=True) With FormRng 'turn calculation to manual before plopping in the formulas Application.Calculation = xlManual .Formula = "=vlookup(a2," & VLookUpAddr & ",10,false)" 'back to automatic Application.Calculation = xlAutomatic 'convert to values .Copy .PasteSpecial Paste:=xlPasteValues 'remove those marching ants/marquee Application.CutCopyMode = False 'get rid of no match and empty cells that came back as 0's .Replace what:="#n/a", replacement:="", _ lookat:=xlWhole, searchorder:=xlByRows, _ MatchCase:=False .Replace what:="0", replacement:="", _ lookat:=xlWhole, searchorder:=xlByRows, _ MatchCase:=False End With End Sub Chris wrote: Thanks Leanne for your reply - much appreciated. I need the vlookup function to repeatly loop through all the thousands of part numbers automatically whilst the macro is running until the last part number has been through the loop. Kind regards, Chris *** Sent via Developersdex http://www.developersdex.com *** -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Macro
Thanks very much Dave for all your excellent help - greatly appreciated.
Cheers, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro Lookup | Excel Worksheet Functions | |||
Run Lookup only once - Macro | Excel Discussion (Misc queries) | |||
lookup macro | Excel Discussion (Misc queries) | |||
Is it possible...lookup macro | Excel Programming | |||
Macro lookup | Excel Discussion (Misc queries) |