Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > For-each syntax problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 175
Default For-each syntax problem
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.

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default For-each syntax problem
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


"excelnut1954" wrote:

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.


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 175
Default For-each syntax problem
On May 4, 10:49 am, Tom Ogilvy
wrote:
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



"excelnut1954" wrote:
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.- Hide quoted text -

- Show quoted text -

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.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default For-each syntax problem
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


"excelnut1954" wrote:

On May 4, 10:49 am, Tom Ogilvy
wrote:
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



"excelnut1954" wrote:
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.- Hide quoted text -

- Show quoted text -

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.


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 175
Default For-each syntax problem
On May 4, 1:45 pm, Tom Ogilvy
wrote:
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



"excelnut1954" wrote:
On May 4, 10:49 am, Tom Ogilvy
wrote:
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

"excelnut1954" wrote:
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.- Hide quoted text -

- Show quoted text -

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.- Hide quoted text -

- Show quoted text -

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.

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 175
Default For-each syntax problem
On May 8, 11:47 am, excelnut1954 wrote:
On May 4, 1:45 pm, Tom Ogilvy
wrote:





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

"excelnut1954" wrote:
On May 4, 10:49 am, Tom Ogilvy
wrote:
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

"excelnut1954" wrote:
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.- Hide quoted text -

- Show quoted text -

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.- Hide quoted text -

- Show quoted text -

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 -

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.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Syntax? MurrayBarn Excel Worksheet Functions 8 June 12th 09 01:45 PM
syntax problem dorre Excel Programming 3 March 20th 06 05:20 PM
Syntax problem unknowndevice[_4_] Excel Programming 4 August 26th 05 10:57 PM
Syntax problem Alex H Excel Worksheet Functions 1 July 2nd 05 08:23 AM
Another Syntax Problem Sharlene England Excel Programming 2 December 2nd 03 10:04 PM


All times are GMT +1. The time now is 12:10 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement