vb "400" error

[Guest]

I am using Excel 2003 and VB for Applications to write a program to keep
track of our orders and inventory

no major problems until now....

i have a decent ammount of debugging experience so tracking down problems is
not too hard (usually....)

I have the following code in a sub, and it is the ONLY code in this
particular sub


<BEGIN VB CODE>
private sub DoesWOExist()
' msgbox("got to a")
Sheets("customer orders").Select
' msgbox("got to b")
Range("d9").Select
' msgbox("got to c")
Do While ActiveCell.Value <> ""
' msgbox(got to d")
If ActiveCell.Value = workOrderNumber Then
woExist = True
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

end sub
<END VB CODE>

i have tried breakpoints and stepping and also the (commented) msgbox to try
and figure out WHAT is causing the error.

when the code is run, (and the msgbox lines are un-commented) i DO get the
following:
got to a
got to b

then I get Microsoft Visual Basic popup window with a Red X and the message
"400". (wthout quotes), and an OK and HELP button. Nothing else. The HELP
button brings up an empty help window.

Range("d9") is empty (this error occurs even with something in that box - or
even with data in D10, D11, D12, etc)

I have looked for help on this error and haven't found anything yet.

My questions are

a) What is the '400' error
and more importantly
b) Why is it giving me that error? - I have used this same code before and
it worked.

Thanks in advance.

 

[Guest]

Not knowing what data you have it is hard to tell but I would clean up the
range line that you have...

private sub DoesWOExist()
' msgbox("got to a")
Sheets("customer orders").Select
' msgbox("got to b")
Sheets("customer orders").Range("d9").Select
' msgbox("got to c")
Do While ActiveCell.Value <> ""
' msgbox(got to d")
If ActiveCell.Value = workOrderNumber Then
woExist = True
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

end sub


Or better yet with a range object

private sub DoesWOExist()
dim rngCurrentCell as range

' msgbox("got to a")
set rngCurrentCell = Sheets("customer orders").Range("d9")
' msgbox("got to c")
Do While rngCurrentCell .Value <> ""
' msgbox(got to d")
If rngCurrentCell .Value = workOrderNumber Then
woExist = True
Else
set rngCurrentCell = rngCurrentCell .Offset(1, 0)
End If
Loop

end sub

HTH

 

[Tom Ogilvy]

I assume this is the red circle with an X and only the 400. I don't know if
there is a given reason for it to occur. anyway, try this revision

private sub DoesWOExist()
Dim rng as Range, rng1 as Range
With Sheets("customer orders")
set rng = .Range(.Range("d9"), .Cells(rows.count,4).End(xlup))
End With
set rng1 = rng.Find(workOrderNumber,, xlValues, xlWhole)
if not rng1 is nothing then
woExist = True
msgbox "Found at row: " & rng1.Row,,workOrderNumber
Else
woExist = False
msgbox "Not found ",,workOrderNumber
End If
end sub

I assume woexist and workOrderNumber are global variables.

 

[Guest]

Thank you. Why that worked, I'm still not sure. I've used the code I had
before and no problems. That got rid of the error - howeveri have the same
error (the red circle with an X and only the 400) popping up on the
following, after it executes the

Cells(Cells.Rows.Count, 4).End(xlUp)(2).Select:

<BEGIN VB CODE>
Private Sub AddToSalesJournal()

Sheets("sales journal").Select
Cells(Cells.Rows.Count, 4).End(xlUp)(2).Select

End Sub
<END VB CODE>

the next line that is excecuted after this sub exits is

MsgBox "Order # " & workOrderNumber & " for " & customerName & " added
successfully."

and the code is done running (or should be).

 

[Tom Ogilvy]

If that code is in a sheet module move it to a general module - the kind you
get when you go to the VBE and do Insert => Module

In a sheet module you can to to the top of the module and in the left
dropdown select worksheet (for example) and in the right one of its events.

That is the only type of code I would put in a sheet module.

Hopefully your declaration of private won't cause scope/visibility
problems. Don't know what is calling what from where.

 

[Guest]

Hmmm - i was trying to keep all of the code that pertains to the particular
sheet on the sheet itself.

i will try this and see what happens.

thanks!

 

[Guest]

apparently that was what was causing my problem. i moved all my code to
modules and seems to be working fine.

thanks!