VBA Type mismatch error on Do While ActiveCell.Value <> ""

Question

Hey I've got code moving rows out to another sheet with the name of the cell, a loop until it hits a blank at the end of the data, an extract of the code here;

Range("AF2").Select
Do While ActiveCell.Value <> ""
    strDestinationSheet = ActiveCell.Value
    ActiveCell.Offset(0, -31).Resize(1, ActiveCell.CurrentRegion.Columns.count).Select
    Selection.Copy

    Sheets(strDestinationSheet).Select
    N = Cells(Rows.count, "AF").End(xlUp).Row
    lastRow = N

    Cells(lastRow + 1, 1).Select
    Selection.PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    Sheets(strSourceSheet).Select
    ActiveCell.Offset(0, 31).Select
    ActiveCell.Offset(1, 0).Select
Loop

However while this part of the code worked fine before on the latest instance of running it now throws up an error on the second line Do While ActiveCell.Value <> "", saying type mismatch. I'm unsure of what's changed to stop this working suddenly, any ideas? Many thanks.

Thanks Scott there are indeed some #N/As on the column, could I just put an error trap to skip when it encounters these? Where exactly would that need to go? — jrb2b, Oct 07 '16 at 15:12
(It's also highly recommended to [avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros)) — BruceWayne, Oct 07 '16 at 15:15
Your code will be much easier to follow/read and maintain/debug, if you properly indent it. — Mathieu Guindon, Oct 07 '16 at 16:35

Scott Craner · Answer 1 · 2016-10-07T15:47:41.783

I personally do not like Do Loops to iterate through a group of cells. I prefer the For Each loop.

Also as was stated by @bruceWayne, avoid using Select as is slows down the code.

Proper indentation makes the code easier to read and avoid simple mistakes.

Dim cel As Range
With Sheets(strSourceSheet)
    For Each cel In .Range("AF2", .Range("AF2").End(xlDown))
        If Not IsError(cel) Then
            strDestinationSheet = cel.Value
            cel.Offset(0, -31).Resize(1, cel.CurrentRegion.Columns.Count).Copy
            N = Sheets(strDestinationSheet).Cells(Sheets(strDestinationSheet).Rows.Count, "AF").End(xlUp).Row
            Sheets(strDestinationSheet).Cells(N + 1, 1).PasteSpecial xlPasteValues
        End If
    Next cel
End With

VBA Type mismatch error on Do While ActiveCell.Value <> ""

1 Answers1