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.
|
Similar Threads
