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