Lookup Macro

[Chris]

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 ***

 

[Leanne]

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]

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 ***

 

[Leanne]

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.

 

[Dave Peterson]

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]

Thanks very much Dave for all your excellent help - greatly appreciated.

Cheers,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***