Here is some code that I'm working on. It's choking on the second loop
statement. I'm getting the Run-time error '1004': Application-defined or
object-defined error. I get this error message all the time and it's driving
me nuts. After some fiddling I'm able to work around it but I can't get past
it this time. Here's the code:

Dim DateValidation As Range
Dim SingleDate As Range
Dim ManningDate As Range
Dim BlankCounter As Integer

Set DateValidation = Application.InputBox(Prompt:="Please select the week(s)
to validate on the Yearly Manning sheet.", _
Title:="Select week(s) to validate", Default:=Selection.Address, Type:=8)
On Error GoTo 0

If DateValidation Is Nothing Then Exit Sub

For Each SingleDate In DateValidation
If Not IsDate(SingleDate) Then
MsgBox "Please select only the week ending dates that you wish
to validate", vbInformation, "Oops, wrong spot"
Exit Sub
End If
Next SingleDate

For Each SingleDate In Sheets("Yearly Manning").Range(DateValidation.Address)

BlankCounter = 0

For Each ManningDate In Sheets("Yearly
Manning").Range(SingleDate.EntireColumn)

If ManningDate.Value = "" Then
BlankCounter = BlankCounter + 1
Else
BlankCounter = 0
End If

If BlankCounter 15 Then
Exit For
Else
'''Do more stuff here
End If

Next ManningDate

Next SingleDate

The error message is coming up on the second loop statement: For Each
ManningDate In Sheets("Yearly Manning").Range(SingleDate.EntireColumn)

What is wrong with using the entire column as my range? As you can I have a
BlankCounter to get out of the loop once I go through enough blank rows.

Can you post some sample data?


--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=66546

Here is one way to write it so it will give you a good range reference:
For Each ManningDate In Sheets("Yearly Manning") _
..Range(SingleDate.Column & ":" & SingleDate.Column)


"Jonathan Brown" wrote:

Here is some code that I'm working on. It's choking on the second loop
statement. I'm getting the Run-time error '1004': Application-defined or
object-defined error. I get this error message all the time and it's driving
me nuts. After some fiddling I'm able to work around it but I can't get past
it this time. Here's the code:

Dim DateValidation As Range
Dim SingleDate As Range
Dim ManningDate As Range
Dim BlankCounter As Integer

Set DateValidation = Application.InputBox(Prompt:="Please select the week(s)
to validate on the Yearly Manning sheet.", _
Title:="Select week(s) to validate", Default:=Selection.Address, Type:=8)
On Error GoTo 0

If DateValidation Is Nothing Then Exit Sub

For Each SingleDate In DateValidation
If Not IsDate(SingleDate) Then
MsgBox "Please select only the week ending dates that you wish
to validate", vbInformation, "Oops, wrong spot"
Exit Sub
End If
Next SingleDate

For Each SingleDate In Sheets("Yearly Manning").Range(DateValidation.Address)

BlankCounter = 0

For Each ManningDate In Sheets("Yearly
Manning").Range(SingleDate.EntireColumn)

If ManningDate.Value = "" Then
BlankCounter = BlankCounter + 1
Else
BlankCounter = 0
End If

If BlankCounter 15 Then
Exit For
Else
'''Do more stuff here
End If

Next ManningDate

Next SingleDate

The error message is coming up on the second loop statement: For Each
ManningDate In Sheets("Yearly Manning").Range(SingleDate.EntireColumn)

What is wrong with using the entire column as my range? As you can I have a
BlankCounter to get out of the loop once I go through enough blank rows.

You asked what is wrong with using EntireColumn as a range. In this case,
there are at least two things wrong. 1. The For Each statement will look
for something that is indexed like a series of cells in a range or a list or
an array. 2. The Range object class is not designed to use EntireRow or
EntireColumn as having the characteristics of an argument for that object
class. So, it tells you that in this case the Application cannot process
that code configuration. The Range object class can accept a single cell
address as a string argument like ("A1") or as series of cell addresses like
("A1:B15, C12, D2:E5") or rows like ("2:5") and columns like ("C:F"). But
EntireRow and EntireColumn alone do not return a string address for a cell
that the Range object class can recognize. But if you add the keyword
Address to them, they should work.

For Each ManningDate In Sheets("Yearly Manning") _
..Range(SingleDate.EntireColumn.Address)


"Jonathan Brown" wrote:

Here is some code that I'm working on. It's choking on the second loop
statement. I'm getting the Run-time error '1004': Application-defined or
object-defined error. I get this error message all the time and it's driving
me nuts. After some fiddling I'm able to work around it but I can't get past
it this time. Here's the code:

Dim DateValidation As Range
Dim SingleDate As Range
Dim ManningDate As Range
Dim BlankCounter As Integer

Set DateValidation = Application.InputBox(Prompt:="Please select the week(s)
to validate on the Yearly Manning sheet.", _
Title:="Select week(s) to validate", Default:=Selection.Address, Type:=8)
On Error GoTo 0

If DateValidation Is Nothing Then Exit Sub

For Each SingleDate In DateValidation
If Not IsDate(SingleDate) Then
MsgBox "Please select only the week ending dates that you wish
to validate", vbInformation, "Oops, wrong spot"
Exit Sub
End If
Next SingleDate

For Each SingleDate In Sheets("Yearly Manning").Range(DateValidation.Address)

BlankCounter = 0

For Each ManningDate In Sheets("Yearly
Manning").Range(SingleDate.EntireColumn)

If ManningDate.Value = "" Then
BlankCounter = BlankCounter + 1
Else
BlankCounter = 0
End If

If BlankCounter 15 Then
Exit For
Else
'''Do more stuff here
End If

Next ManningDate

Next SingleDate

The error message is coming up on the second loop statement: For Each
ManningDate In Sheets("Yearly Manning").Range(SingleDate.EntireColumn)

What is wrong with using the entire column as my range? As you can I have a
BlankCounter to get out of the loop once I go through enough blank rows.