Hello,

Could someone please help me with the following VBA code that Dave
Peterson kindly gave me?

When I run it from a Module, I keep getting a run-time error 9
"subscript out of range" message.

Thanks,

Chris.

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


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