Run-time error 9 - Subscript out of range

[Chris Hankin]

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

 

[Dave Peterson]

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.

 

[Joel]

You didn't indiczte which line was highlighted when the error occur so it is
difficult to give you an exact answer.

I would think one of these two lines are at fault

Set MstrWks = Workbooks("master.xls").Worksheets("master")
Set StockNumWks = Workbooks("stock numbers.xls").Worksheets("Stock
Numbers")

Make sure the workbooks are opened, the book names are spelled correctly,
and make sure the worksheets in each workbook is spelled correctly. If
either of the workbooks are nes workbooks make sure you save them once before
running the macro. Unsaved workbooks will not have the XLS extension and wil
bed czlled book1, book2, ...

 

[Chris Hankin]

Hi Dave,

Sorry I did not indicate which line caused the error message.

It was this line that was highlighted in yellow by the Visual Basic
Editor:

Set StockNumWks = Workbooks("stock numbers.xls").Worksheets("Stock
Numbers")

Also, I copied your VBA code into a module - I hope this was OK?

Kind regards,

Chris.

 

[Chris Hankin]

Hi Joel and Dave,

The VBA error I was getting has gone because I did not realise that I
had to open both workbooks.

Thanks again for your help - very much appreciated.

Kind regards,

Chris.