For-each syntax problem

E

excelnut1954

This macro will copy rows from one workbook to another based on what
warehouse location (row) they are in.

Near the bottom, you'll see where I note where I'm getting an error.
I'm sure it's something to do with either the Dim statements, or maybe
the syntax of the line I'm getting the error in.The error is "Object
variable or With block variable not set".
CycleCount_Monday, Tues, Wed, etc are range names containing the
warehouse rows to count that day.
DayOfWeek is range that will show the value of the next workday.
Mon=2, Tues=3, Wed=4, etc.

Sub CycleCount()

Dim RowsToCount As Range
Dim CycleCount_Monday As Object
Dim CycleCount_Tuesday As Object
Dim CycleCount_Wednesday As Object
Dim CycleCount_Thursday As Object
Dim CycleCount_Friday As Object
Dim DayOfWeek As Integer

'Opens up the Blank inventory workbook (target)
ChDir "S:\Furniture Staging List\Staging List Inventories"
Workbooks.Open Filename:= _
"S:\Furniture Staging List\Staging List Inventories\Inventory
Wk of BLANK.xls"

'Switch back to original (source) file
ThisWorkbook.Activate
Worksheets("Cycle Count").Activate

If Range("DayOfWeek") = 2 Then
Set RowsToCount = CycleCount_Monday
ElseIf Range("DayOfWeek") = 3 Then
Set RowsToCount = CycleCount_Tuesday
ElseIf Range("DayOfWeek") = 4 Then
Set RowsToCount = CycleCount_Wednesday
ElseIf Range("DayOfWeek") = 5 Then
Set RowsToCount = CycleCount_Thursday
ElseIf Range("DayOfWeek") = 6 Then
Set RowsToCount = CycleCount_Friday

End If


'This sets the range name TempColumnName to 1st Row in Official List
Worksheets("Official List").Activate
Application.Goto Reference:="FirstRowOfficialList"
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="TempColumnName", RefersToR1C1:= _
"='Official List'!R6C3:R416C3"

Range("FirstRowOfficialList").Select
ActiveCell.Offset(1, 0).Select

For Each cell In Range("TempColumnName")
'''If cell.Value = "R10" Then 'this worked when I plugged in
one of the row numbers instead of a variable as in next line.
If cell.Value = RowsToCount Then 'THIS IS WHERE I'M GETTING
ERROR

CopyRows 'sub to perform copy routine

End If

ActiveCell.Offset(1, 0).Select

Next cell

ThisWorkbook.Activate
ActiveWorkbook.Names("TempColumnName").Delete

End Sub

Any suggestions would be appreciated.
Thanks,
J.O.
 
T

Tom Ogilvy

That would be consistent with Range("DayOfWeek") not containing a 2, 3, 4, 5,
or 6 in which case it is never set to anything.
 
E

excelnut1954

That would be consistent with Range("DayOfWeek") not containing a 2, 3, 4, 5,
or 6 in which case it is never set to anything.

--
Regards,
Tom Ogilvy




















- Show quoted text -
Click to expand...

I'm sorry. I don't understand. DayofWeek is the name of a cell that
always has a value. There's a formula there that will assign the
numeric value of what ever is the next workdate. It will change each
day.
Can you give a bit more to your answer?
Thanks for your time.
J.O.
 
T

Tom Ogilvy

Sub CycleCount()

Dim RowsToCount As Range
Dim CycleCount_Monday As Object
Dim CycleCount_Tuesday As Object
Dim CycleCount_Wednesday As Object
Dim CycleCount_Thursday As Object
Dim CycleCount_Friday As Object
Dim DayOfWeek As Integer

'Opens up the Blank inventory workbook (target)
ChDir "S:\Furniture Staging List\Staging List Inventories"
Workbooks.Open Filename:= _
"S:\Furniture Staging List\Staging List Inventories\Inventory
Wk of BLANK.xls"

'Switch back to original (source) file
ThisWorkbook.Activate
Worksheets("Cycle Count").Activate

If Range("DayOfWeek") = 2 Then
Set RowsToCount = CycleCount_Monday
ElseIf Range("DayOfWeek") = 3 Then
Set RowsToCount = CycleCount_Tuesday
ElseIf Range("DayOfWeek") = 4 Then
Set RowsToCount = CycleCount_Wednesday
ElseIf Range("DayOfWeek") = 5 Then
Set RowsToCount = CycleCount_Thursday
ElseIf Range("DayOfWeek") = 6 Then
Set RowsToCount = CycleCount_Friday

End If


'This sets the range name TempColumnName to 1st Row in Official List
Worksheets("Official List").Activate
Application.Goto Reference:="FirstRowOfficialList"
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="TempColumnName", RefersToR1C1:= _
"='Official List'!R6C3:R416C3"

Range("FirstRowOfficialList").Select
ActiveCell.Offset(1, 0).Select

For Each cell In Range("TempColumnName")
if Not RowsToCount is nothing then
If cell.Value = RowsToCount Then
CopyRows 'sub to perform copy routine
else
msgbox "Nothing copied because " & vbNewLine & _
"Value of Range DayofWeek is " & Range("DayofWeek").Value
end if
End If

ActiveCell.Offset(1, 0).Select

Next cell

ThisWorkbook.Activate
ActiveWorkbook.Names("TempColumnName").Delete

End Sub
 
E

excelnut1954

Sub CycleCount()

Dim RowsToCount As Range
Dim CycleCount_Monday As Object
Dim CycleCount_Tuesday As Object
Dim CycleCount_Wednesday As Object
Dim CycleCount_Thursday As Object
Dim CycleCount_Friday As Object
Dim DayOfWeek As Integer

'Opens up the Blank inventory workbook (target)
ChDir "S:\Furniture Staging List\Staging List Inventories"
Workbooks.Open Filename:= _
"S:\Furniture Staging List\Staging List Inventories\Inventory
Wk of BLANK.xls"

'Switch back to original (source) file
ThisWorkbook.Activate
Worksheets("Cycle Count").Activate

If Range("DayOfWeek") = 2 Then
Set RowsToCount = CycleCount_Monday
ElseIf Range("DayOfWeek") = 3 Then
Set RowsToCount = CycleCount_Tuesday
ElseIf Range("DayOfWeek") = 4 Then
Set RowsToCount = CycleCount_Wednesday
ElseIf Range("DayOfWeek") = 5 Then
Set RowsToCount = CycleCount_Thursday
ElseIf Range("DayOfWeek") = 6 Then
Set RowsToCount = CycleCount_Friday

End If

'This sets the range name TempColumnName to 1st Row in Official List
Worksheets("Official List").Activate
Application.Goto Reference:="FirstRowOfficialList"
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="TempColumnName", RefersToR1C1:= _
"='Official List'!R6C3:R416C3"

Range("FirstRowOfficialList").Select
ActiveCell.Offset(1, 0).Select

For Each cell In Range("TempColumnName")
if Not RowsToCount is nothing then
If cell.Value = RowsToCount Then
CopyRows 'sub to perform copy routine
else
msgbox "Nothing copied because " & vbNewLine & _
"Value of Range DayofWeek is " & Range("DayofWeek").Value
end if
End If

ActiveCell.Offset(1, 0).Select

Next cell

ThisWorkbook.Activate
ActiveWorkbook.Names("TempColumnName").Delete

End Sub

--
Regards,
Tom Ogilvy






- Show quoted text -
Click to expand...

I really appreciate the help, Tom. But, though I'm not getting any
errors now with your code changes, it doesn't copy anything. I don't
receive any kind of message. It brings up the target workbook, and I
can see the cursor ends up at the bottom of the list it's reading.
And, it's in the correct column. But, that's all it does. I've checked
all the range names involved, and I can't see anything wrong.
I put a MsgBox at the 1st line of the other sub, CopyRows, just to see
if it tried to go there. But the MsgBox didn't appear, so it's not
trying to execute that sub.

I've tried examining this myself the last day, and I just can't see
anything wrong with any of my ranges or range names.
Is there anything else I can try?
At least I got to understand better the concept of the If Not/Is
Nothing statement you added.
Thanks again,
J.O.
 
E

excelnut1954

I really appreciate the help, Tom. But, though I'm not getting any
errors now with your code changes, it doesn't copy anything. I don't
receive any kind of message. It brings up the target workbook, and I
can see the cursor ends up at the bottom of the list it's reading.
And, it's in the correct column. But, that's all it does. I've checked
all the range names involved, and I can't see anything wrong.
I put a MsgBox at the 1st line of the other sub, CopyRows, just to see
if it tried to go there. But the MsgBox didn't appear, so it's not
trying to execute that sub.

I've tried examining this myself the last day, and I just can't see
anything wrong with any of my ranges or range names.
Is there anything else I can try?
At least I got to understand better the concept of the If Not/Is
Nothing statement you added.
Thanks again,
J.O.- Hide quoted text -

- Show quoted text -
Click to expand...

After fooling around with this thing more, I can to the conclusion
that the following section might be the problem. The RowsToCount value
is not being assigned.

'Determines what the next work day is.
If Range("DayOfWeek") = 2 Then
Set RowsToCount = CycleCount_Monday
ElseIf Range("DayOfWeek") = 3 Then
Set RowsToCount = CycleCount_Tuesday
ElseIf Range("DayOfWeek") = 4 Then
Set RowsToCount = CycleCount_Wednesday
ElseIf Range("DayOfWeek") = 5 Then
Set RowsToCount = CycleCount_Thursday
ElseIf Range("DayOfWeek") = 6 Then
Set RowsToCount = CycleCount_Friday

Maybe it's the Set statements.

When this is run, there is no error, nor any message given. The cursor
ends up at the bottom of the list as it should, but it is not
recognizing the table of values which should pass from
CycleCount_Mon, Tues, etc. to RowsToCount. Also, control is not passed
to the CopyRows sub, which is further down the macro.

If anyone can come up some ideas on what I could try, please let me
know.
Thanks,
J.O.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

For-each to copy rows to another workbook 5
For-Each loop, I think. 1
Another For each headache I get 6
A For Each in Range... with a .Find(what:="TJ", ... in it problem 15
Mail a different file(s) to each person in a range 0
Problem with hyperlink macro 0
Combine Two Different Worksheet_Change codes 9
Workaround for Item Count > 500 0

Top