General Help: Gettign 424 Must be simple

[JPARKHURST]

Getting Runtime error 424 Object Expected.

Attempting to get several functions to run, I'm encountering an error from go. I still have much learnign to do in Excel, I'm not really sure what to research on this, though. Any help is welcome.

Error is occurring on the second line where I am attempting to setup a range.

Here is the complete sub:

Sub PrepareSchedule()
Dim rngJobDispatch As Range
rngJobDispatch = wbSchedule.wsJobDispatch.UsedRange

'Clear Worksheet we will be moving data to3
ClearWorksheet wsWorkingDispatch, "D"
MsgBox ("ClearWorksheet has been completed")

'Sort Range
SortRange (rngJobDispatch)
MsgBox ("SortRange has been completed")

'Replace Assy & Pack
ReplaceAP
MsgBox ("Replace Assy & Pack has been completed")

'Copy Job_Dispatch Jobs List to Working_Dispatch
wsJobDispatch.Range("B:B").Copy (wsWorkingDispatch.Range("D:D"))
MsgBox ("Jobs have been copied from Job_Dispatch to Working_Dispatch")

'Clear Shortage Report to Prepare for Dump
ClearWorksheet wsShortages, "B"
MsgBox ("Shortage Report has been cleared and is ready for dump.")

End Sub


TIA,

Jon

 

[Fluff]

All objects need to be Set when you assign something to them.
Therefore add the word Set to the beginning of your 2nd line

 

[JPARKHURST]

All objects need to be Set when you assign something to them.
Therefore add the word Set to the beginning of your 2nd line

Oh, wow. Of course, thank you very much for the second set of eyes. >.<

 

[Fluff]

Glad to help & thanks for the feedback

 

[JPARKHURST]

Still having some issues. Same error, 424:

Sub PrepareSchedule()
Dim rngJobDispatch As Range
Set rngJobDispatch = wbSchedule.wsJobDispatch.UsedRange

Any thoughts?

 

[JPARKHURST]

Placement/order of operations I may be having some problems with.

I have placed the following declarations in my ThisWorkbook (General) (Declarations) file. Is this the proper placement? I'm firing my PrepareSchedule from a button, but perhaps if my declarations are in the wrong place they may not be getting setup?

'Option Explicit
Public wbSchedule As Workbook


Public wsWorkingDispatch As Worksheet
Public wsShortages As Worksheet
Public wsDashboard As Worksheet
Public wsInstructions As Worksheet
Public wsJobDispatch As Worksheet
Public wsNotes As Worksheet
Sub Workbook_Open()
Set wbSchedule = ThisWorkbook
Set wsWorkingDispatch = wbSchedule.Worksheets("Working_Dispatch")
Set wsShortages = wbSchedule.Worksheets("Shortages")
Set wsDashboard = wbSchedule.Worksheets("Dashboard")
Set wsInstructions = wbSchedule.Worksheets("Instructions")
Set wsJobDispatch = wbSchedule.Worksheets("Job_Dispatch")
Set wsNotes = wbSchedule.Worksheets("Notes")

End Sub

 

[Fluff]

This line is wrong

Set rngJobDispatch = wbSchedule.wsJobDispatch.UsedRange

When you set a worksheet variable you are also referencing the workbook it's in.
Therefore when using that variable you cannot also reference the workbook as you have done, so that line should become

Set rngJobDispatch = wsJobDispatch.UsedRange

Hope that makes sense

 

[JPARKHURST]

This line is wrong

Set rngJobDispatch = wbSchedule.wsJobDispatch.UsedRange

When you set a worksheet variable you are also referencing the workbook it's in.
Therefore when using that variable you cannot also reference the workbook as you have done, so that line should become

Set rngJobDispatch = wsJobDispatch.UsedRange

Hope that makes sense

Makes perfect sense. Thank you very much with your help. Kinda a strange language to work around in, but hopefully getting a handle on it. Do I need to make sure I set an Active book, or is it just assuming that is coming from the form origin?

 

[Fluff]

Because you have this in the Workbook_open event

Set wbSchedule = ThisWorkbook
  Set wsWorkingDispatch = wbSchedule.Worksheets("Working_Dispatch")

You are stipulating that the sheet is in ThisWorkbook (ie the one containing the macro).
However if you did this

Set wbSchedule = ActiveWorkbook
  Set wsWorkingDispatch = wbSchedule.Worksheets("Working_Dispatch")

you would be stipulating that the sheet is in the activeworkbook at the time the variable was set (which in this case would also be ThisWorkbook).
Whereas with this

Set wsWorkingDispatch = Worksheets("Working_Dispatch")

you would be Implying that the sheet is in the activeworkbook as you haven't stipulated anything

 

[JPARKHURST]

OK, awesome, thank you very much. That helps me tremendously.

Jon