Alas, I've tried adding both of those, and it didn't fix
the problem. Good thought, though. I even added a few
libraries that I thought *might* be relevant, but no luck.

Ah well. Back to the drawing board.

Jason


-----Original Message-----
Hi Jason, just a thought , I had some code that wouldnt
work once and I was
told to set the references in VB etc. you may need to
enable the references
in the VB environment to include "Visual Basic for
Applications" and
"Microsoft Object Library"

Ie. Open the VB workspace then select Tools - References
and click the
check box for the above references.

Fred

"jason" wrote in
message
...
Hi, I'm trying to use VB to alter one worksheet based
on a
roster of names in another worksheet (in the same
workbook). I'm using Excel 2000 version 9.0.2720
(Windows
2000) and Visual Basic 6.0.8435.

Each time the code gets to a place (any place) where it
is
using a range, the code stops executing and VB reports
the
following: "Visual Basic 400". Nothing else! Just -
"400."

I suspect that, since I'm new to VB, I'm not setting
things up correctly. Here is a typical function (see
***
for the line where things are breaking down):

Private Sub addNewList(ByVal numEmpl As Integer)

Dim mySheetName As String
Dim mySheet As Worksheet
Dim myRange As Range

Dim numOfWeeks As Integer
Dim numOfShifts As Integer
Dim baseCell As Range
Dim cellFormula As String
Dim initials As String

'Initialize variables
mySheetName = Worksheets("ROSTER").Range("E7").Value
Set mySheet = Worksheets(mySheetName)
numOfWeeks = Worksheets("Roster").Range("E10").Value
numOfShifts = numOfWeeks * 17

'Get to our starting point
mySheet.Select
Set myRange = Range("B5")
*** myRange.Select

If anyone can help, I would greatly appreciate it.


.