For Each Cell in Column not working as expected

Question

Sub Chart_Laid(StartC As Long, StartR As Long, StartD As Date, EndD As Date)

Dim Cell As Range
Dim RowShift As Integer
Dim ColShift As Integer
Dim TimeCheck As Double
Dim wsLaid As Worksheet, wsSleepChart As Worksheet


Set wsLaid = Worksheets("Sheet2")
Set wsSleepChart = Worksheets("Sleep Chart")

With wsLaid
    For Each Cell In .Columns(3)
'****************************************************
        If Cell = "Laid Down" Then
            TimeCheck = .Cells(Cell.Row, 2).Value
'****************************************************
            If After_Midnight(TimeCheck) Then
                RowShift = -1
                ColShift = 0
            Else
                RowShift = 0
                ColShift = Application.WorksheetFunction.Round(-2 * (0.5 / (0.25 / 24)), 0)
            End If
            With wsSleepChart.Cells(.Cells(Cell.Row, StartC - 51).Value - StartD + StartR + RowShift, Application.WorksheetFunction.Round((.Cells(Cell.Row, StartC - 50).Value) / (0.25 / 24), 0) + StartC + ColShift).Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Color = -16776961
                .Weight = xlThick
            End With
        End If
        If IsEmpty(.Cells(Cell.Row, 1)) Then
            If .Cells(Cell.Row - 1, 1) = EndD Then Exit Sub
        End If
    Next Cell
End With
End Sub

I am getting a Type mismatch error in either the 1st or second line between the ***** lines. I had this working, then I was stupid and went through and cleaned a bunch of things up and now I do not recall what I changed to make it stop working.

UPDATE:

The error occurs when stepping through the sub when the following line is selected and I press F8.

        If Cell = "Laid Down" Then

I have also tried

If Cell.Value = "Laid Down" Then

and

If Cell.Value2 = "Laid Down" Then

all produce the error.

`Cell` and `Cell.Value` is synonym in this context, that's not your issue. You should check (with the debugger) what the content of the cell is when the error occurs: Likely it has an error (eg #N/A or #Div0 or the like), and you can't compare an error and a string. You can use `iserror(Cell)` to check if the cell has an error. — FunThomas, Jul 07 '23 at 10:56
Nothing looks wrong in the code syntax, so the respective cell (real) value must be the issue (an error), as stated in the above comment. — FaneDuru, Jul 07 '23 at 11:02
apparently there is a difference in the way the variable `Cell` deals with the results from `.columns(3)` and `.Range("C:C")` I was operating under the premis that both would return a single cell. This is not the case with `.columns(3)` as Ed2 point out in the answer below. — Forward Ed, Jul 07 '23 at 11:04
@FaneDuru They both return the same address yet if you don't add `.Cells` after `.Columns(...)` in a loop, `cell` becomes the whole column. Try e.g. (this is wrong): `For Each cell In Sheet1.Range("A1:J10").Columns(1): Debug.Print cell.Address: Next cell` and the result will be a single address: `$A$1:$A$10`. — VBasic2008, Jul 07 '23 at 22:34
@GSerg Thanks for sharing the [link](https://stackoverflow.com/a/51097214). Very educational. — VBasic2008, Jul 07 '23 at 23:21
@VBasic2008 @ed2: **I was wrong** and I deleted my comment. The default property of range depends on the way it has been created. I **always** try not using the default property and (probably) that's why I never faced such a 'problem' I mean, I always use `For each cel in rng.cells` , independent of the range way of building or `ar = rng.value`. It is a good habit, I think, but also makes you not observe the differences of the default property in case of different ranges... Anyhow, I consider this question more instructive than I thought when I've just seen it... — FaneDuru, Jul 08 '23 at 19:19
While I do not believe that it is the same question as the one linked duplicate, I do believe the currently accepted answer to the duplicated question is SPOT ON! — Forward Ed, Jul 12 '23 at 01:40

VBasic2008 · Answer 1 · 2023-07-07T22:08:10.647

Avoid Type Mismatch Error

Dim cell As Range, tValue, TimeCheck As Double

With wsLaid
    ' Tell VBA that you're looping through cells.
    ' Since you're dealing with worksheet columns, use column strings
    ' to make it immediately obvious what's happening.
    ' Looping through a million cells may take forever.
    ' Use e.g. 'UsedRange' to reference the relevant (smaller) range.   
    For Each cell In Intersect(.UsedRange, .Columns("C")).Cells
        ' Ignore case with 'vbTextCompare' or don't with 'vbBinaryCompare'.
        ' Use 'CStr' to avoid an error if the cell contains an error value.
        If StrComp(CStr(cell.Value), "Laid Down", vbTextCompare) = 0 Then
            tValue = .Cells(cell.Row, "B").Value
            If VarType(tValue) = vbDouble Then ' is a number
            ' or:
            'If IsNumeric(tValue) Then ' Note that empty cells are numeric!
                
                TimeCheck = CDbl(tValue) ' or just 'TimeCheck = tValue'
                
                ' irrelevant code
                
            End If
        End If
    Next cell
End With

ed2 · Accepted Answer · 2023-07-08T01:39:50.367

Why it's happening

The line For Each Cell In .Columns(3) will return a range in the variable Cell, which is a whole column tall.

Solution 1

Replacing .Columns(3) with .Range ("C:C") means you don't need the extra layer of item nesting (cell within range within column within worksheet).

Solution 2

Alternatively, to keep it as .Columns(3), you would need to further iterate through the individual cells in that column, comparing each cell value to the string (not just the column value, which will also return a type mismatch error unless the column range Cell is only one cell tall).

So, you might replace:

With wsLaid
    For Each Cell In .Columns(3)

with something like:

With wsLaid
    For Each Column In .Columns(3)
       For Each Cell In Column.Cells

(and make the attendant Next Column near the end of your code accordingly).

Why the confusion?

Don't forget the way these ranges nest:

Columns

The object .Columns(3) is a collection of ranges, each range is a single column tall. (Don't be confused by the fact that in this instance there is only one such column-range in the collection, as only a single column is specified). Within that range, in turn, is a collection of cells.

So, a collection of COLUMNS consists of one (in this case) or more RANGES and each of those RANGES contains some individual CELLS:

Cell is in a Range is in a Column, which is a range object that has been specified. Cell is 3 levels deep.

Range

Conversely, the object .Columns(C:C) refers directly to a range of cells. In this case, the cells specified are all those cells in column C.

Cell is in a Range which is a range object that has been specified. Cell is only 2 levels deep.

@FunThomas yes, was catching if the default `.Value` was throwing when `.Value2` may work. But, that distinction is not the issue here since they both throw the type mismatch error. — ed2, Jul 07 '23 at 10:56
@ForwardEd So if you replace `.columns(3)` with `.Range("C:C")` does it also fix the original issue? Or does issue 2 still prevail? — ed2, Jul 07 '23 at 11:00
I so poorly worded my previous comment. I meant to say that `.range("C:C")` solved the problem. — Forward Ed, Jul 07 '23 at 11:01