Another couple of guesses:

1. Did you ever declare a variable called "range"?
2. You may want to run Rob Bovey's code cleaner:
You can find it at:
http://www.appspro.com/
3. Is your code behind a worksheet? I got a 1004 (not 400) error when I tried
to select a range that wasn't fully qualified. (Unqualified ranges belong
to the worksheet owning the code--even if you've selected another sheet
first.)

with mySheet
.Select
Set myRange = .Range("B5")
end with
myRange.Select

So now that .range("b5") belongs to mySheet--not the sheet owning the code.

4. When you were adding references, did you notice if any reference was
missing? If yes, try removing it (or finding it) and see if that helps.


Jason wrote:

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.


.


--

Dave Peterson