Which line causes the error?

Subscript out of range means that something referred to in that code doesn't
exist.

So if this line causes the error:
Set MstrWks = Workbooks("master.xls").Worksheets("master")

It could mean that there isn't a workbook named master.xls that's open.

Or it could mean that there is a workbook named master.xls that is open, but
that workbook doesn't have a worksheet named Master.

Same kind of thing with the stock numbers line.

Chris Hankin wrote:

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

--

Dave Peterson