subscript out of range error in excell

[Lilivati]

I am trying to work with two workbooks, with workbook A looking up
information in workbook B. When workbook A opens it prompts the user
to open workbook b:

Public modelfile As String
Public nassisfile As String

'on open event for workbook A
Sub Workbook_Open()

modelfile = ThisWorkbook

If nassisfile = "" Then
MsgBox "Please open a NASSIS reference file."
nassisfile = Application.GetOpenFilename("Excel Files,*.xls", 1,
"Select a NASSIS file.")
Workbooks.Open nassisfile
Workbooks(modelfile).Activate
End If

End Sub


This part works perfectly. The reference file (workbook B) opens as
instructed and workbook A is reactivated.

Then in the worksheet change event for workbook A I have the following:

Sub Worksheet_Change(ByVal Target As Range)
Dim Val As Range
Dim cell As Range
Dim part As String
Dim desc As String
Dim mlUsed As Range
Dim msg As String

'here is where the error occurs
Set mlUsed = Workbooks("nassisfile").Worksheets(2).Range("C")


Set Val = Range("B:B")

For Each cell In Target
If Union(cell, Val).Address = Val.Address And cell.text <> "" Then
part = cell.text
desc = WorksheetFunction.VLookup(part, mlUsed, 2, False)
cell.Offset(0, 2) = desc
End If
Next cell

End Sub

Now workbook B (nassisfile) definitely exists- the user just opened it!
Yet I am getting this error. This is very frustrating as this code
worked perfectly when I left it on Friday, and when I solved a
different problem today it no longer works. I eliminated all the
changes I made today and it still will not function. Please help!

 

[Ardus Petus]

Try:
Set mlUsed = Workbooks(nassisfile).Worksheets(2).Range("C")
in order to access to value of variable nassisfile

HTH

 

[Lilivati]

Try:
Set mlUsed = Workbooks(nassisfile).Worksheets(2).Range("C")
in order to access to value of variable nassisfile

HTH

I tried that, and it still gives me the same error. It is like it is
somehow not remembering the variable.

If I ask a message box to display the value of nassisfile, the first
time I try to change a cell it will display the name, and give me the
error. The next time I try it the message box is blank (it has lost
all touch with the variable nassisfile).

I tried replacing my first code with:

Public modelfile As String
Public nassislist As String

Public Sub Workbook_Open()
modelfile = ThisWorkbook.Name
nassisfile = "masterlist.xls"
End Sub


just as a way of bugtesting, since the value of nassisfile must
ultimately be defined by the user. The same error results.

 

[Lilivati]

Alright, ignore that previous message...I made a silly mistake with the
naming and the simplified version

Public Sub Workbook_Open()
modelfile = ThisWorkbook.Name
nassisfile = "masterlist.xls"
End Sub

works. However when I try to define nassisfile in the if loop I have
in the first post the worksheet change script is unable to find the
nassisfile variable.

 

[Lilivati]

The problem seems not to be in the if loop but in the
Application.GetOpenFilename procedure. It is simply not acknowledging
that this is defining nassisfile! It had no problems with this on
Friday, this is driving me nuts!