For Each xxx in Column not working as expected

Question

I am trying to convert all string dates to number for an entire column. Based on this Question, I initially tried:

.range(.cells(headerrow+1, ImpCol.column),.cells(LastRow, ImpCol.column)) =CDate(.range(.cells(headerrow+1, ImpCol.column),.cells(LastRow, ImpCol.column)))

then I thought I may need value, so I tried the following:

.range(.cells(headerrow+1, ImpCol.column),.cells(LastRow, ImpCol.column)).value =CDate(.range(.cells(headerrow+1, ImpCol.column),.cells(LastRow, ImpCol.column)).value)

.range(.cells(headerrow+1, ImpCol.column),.cells(LastRow, ImpCol.column)) =CDate(.range(.cells(headerrow+1, ImpCol.column),.cells(LastRow, ImpCol.column)).value) 

.range(.cells(headerrow+1, ImpCol.column),.cells(LastRow, ImpCol.column)).value =CDate(.range(.cells(headerrow+1, ImpCol.column),.cells(LastRow, ImpCol.column)))

So then I did some more research, found this question, and I am guessing that I need to go cell by cell so I tried setting up a for each loop:

'I tired both dims spearately, one at a time.  just listing what I have tried.
Dim rngCell as range
Dim rngCell as variant

For Each rngCell In Source.Worksheets("Sheet1").Columns(ImpCol.Column)
    If rngCell.Row > HeaderRow Then rngCell.Value = CDate(rngCell.Value)
Next rngCell

When I step through, ImpCol.Column has a value of 7 as I expected. Since I was just supplying a single column I was expecting rngCell to take the value of the contents of each cell one at a time. Instead it grabs the whole column, performs one check, and does not loop.

I know how to do it in a For X= HeaderRow +1 to LastRow, but I do not understand how to make FOR EACH work in this case.

What adjustment do I need to make to have it step through each cell in a FOR EACH loop?

`For Each rngCell In Source.Worksheets("Sheet1").Columns(ImpCol.Column).Cells` but I think there must be a better way of doing this ... — SJR, Feb 04 '20 at 20:13
@cybernetic.nomad as the comment line above it said, I tried each one separately. seeing if using a different DIM assignment would make a different. — Forward Ed, Feb 04 '20 at 20:14
@SJR the .cells makes more sense to me. prior to that the range of the column was just being put in rngcells. If there is a better way to convert the column, I am all for it. Just read another post where someone did a VBA recording of text-columns. not sure if that is a better route or not. I am mainly still just trying to understand for each better. so I can code a little better than just using `for x = 1 to end` all the time — Forward Ed, Feb 04 '20 at 20:16
adding .cells to the end of the "IN" locations such that its a bunch of cells that is being looked at instead of the column made it work. I had tried to see if I could assign `DIM RNCCELL as Cell` or the like, but there was no cell(s) option. — Forward Ed, Feb 04 '20 at 20:23
`Range` is the correct variable type (there is no `Cells` type). What sort of entries do you have in your cells? — SJR, Feb 04 '20 at 20:44
I'm not sure actually why you have to specify `Cells` in such cases when you don't for a normal `Range`. — SJR, Feb 04 '20 at 20:50
@SJR - because the object iterated over is a `Column` - the whole range. A `Column` or `Row` is the entire `Range`, not the cells that comprise it. Perhaps easier to understand where the loop is through multiple columns - `For each col in myRange.Columns` - it loops column-by-column, not cell-by-cell. This has bit me in the butt before. — BigBen, Feb 04 '20 at 21:26
@BigBen since I am referencing a single column, could I have also used `.rows` instead of `.cells`? — Forward Ed, Feb 04 '20 at 22:15
@BigBen - sure but a column is still just a range of a million plus cells. I could specify a range of a similar size. — SJR, Feb 04 '20 at 22:41
@SJR - but it's the column considered *as a column*... that's the smallest unit, so to say. `Range("A:A")` doesn't work the same way as `Columns("A")`. There's a dupe somewhere that I think Matthieu Guindon answered. — BigBen, Feb 04 '20 at 22:45

score 1 · Accepted Answer · answered Feb 04 '20 at 20:37

1

In general, when you are looping through something, it is always a good idea to Debug.Print iterator.Address, or in the case of the OP's code Debug.Print rngCell.Address.

Adding a .Cells at the loop of column would make it work:

Sub TestMe()

    Dim myCell As Range
    Dim i As Long

    'filling the first 20 cells in column A with values
    For Each myCell In ThisWorkbook.Worksheets(1).Range("A1:A20")
        i = i + 1
        myCell = DateSerial(2020, 1, 1 + i)
    Next


    For Each myCell In ThisWorkbook.Worksheets(1).Columns("A").Cells
        If Len(myCell) > 0 Then
            With myCell
                .Value = CDate(myCell)
                .NumberFormat = "General"
            End With
        Else
            Exit Sub
        End If
    Next

End Sub

The second loop in the code here would run about 1.048.576 times (2^20), if you remove the Exit Sub, which could be ok, depending on the business logic.

answered Feb 04 '20 at 20:37

Vityata

42,633
8
55
100

while you were posting your answer I was just running into this problem. I thought for each loops we supposed to stop when the remainder of the entries were empty, null, nothing....and maybe is me thinking of those for the moment as the same when there are technical difference. I was just about to post if I needed to check for empty/nothing or the like, and you have it right in your loop along with the formatting I needed to added. Was going to ask if it is better practice to added the formatting in the lop or to the whole range outside the loop. – Forward Ed Feb 04 '20 at 20:55
with having to put in a check for empty cell case, (which I have in rows 1 and 2), I can change the logic check to `rngCell.row> headerrow and not isempty(rngcell)` or something similar. In a case like this is there an advantage to a `for each` loop over a `for x =headerrow+1 to lastrow` loop? – Forward Ed Feb 04 '20 at 21:00
@ForwardEd - I guess `ThisWorkbook.Worksheets(1).Columns("A").NumberFormat = "General"` would be way faster. – Vityata Feb 04 '20 at 21:00
1

@ForwardEd - in general, the `for each` loop is a bit slower than the `for x = n to m`. https://stackoverflow.com/questions/10411871/why-is-foreach-do-sometimes-slower-than-for and https://stackoverflow.com/questions/5007458/problems-using-foreach-parallelization – Vityata Feb 04 '20 at 21:02
1

Thank you for the links to those questions. They were over my head on the technical side of things but the gist that they are slower helps! – Forward Ed Feb 04 '20 at 21:08
@ForwardEd - there's a question out there that says to use `For Each` with a `Collection` - but I can't find it :( – BigBen Feb 04 '20 at 21:15
@BigBen It may have been my question! Sounds familiar...just dont know if I voiced it ever – Forward Ed Feb 04 '20 at 21:20

For Each xxx in Column not working as expected

1 Answers1