Loop Crashing Excel VBA

Question

i have been having problems with getting my code to run through its conditional loop and stopping. Here is what I have:

  Do While True
    Dim i As Integer
    i = 2
    If Cells(i, 1).Value <> "" And Not IsError(Cells(i, 2).Value) Then
        Range(Cells(i, 1), Cells(i, 22)).Copy
        Range(Cells(i, 1), Cells(i, 22)).PasteSpecial
        i = i + 1
    Else
        Exit Do
    End If
  Loop

What I'm trying to do is to get the program to check if one cells isn't empty and if another doesn't have an error in it, if that condition is met, then the program would copy a certain row and re-paste it as just it's values since some of the cells in the row is a formula. For some reason the loop doesn't exit and Excel crashes, am I missing something?

Your value of i never changes. i = i + 1 is fine, but you then reset it to 2 every loop. — Skaterhaz, Jun 24 '16 at 14:44
Your loop is exiting when true, so I'd suggest that the criteria are never being met. — Nathan_Sav, Jun 24 '16 at 14:44

score 3 · Answer 1 · answered Jun 24 '16 at 14:44

the i = 2 should be outside

Dim i As Integer
i = 2
Do While True

If Cells(i, 1).Value <> "" And Not IsError(Cells(i, 2).Value) Then
    Range(Cells(i, 1), Cells(i, 22)).Copy
    Range(Cells(i, 1), Cells(i, 22)).PasteSpecial
    i = i + 1
Else
    Exit Do
End If
Loop

zatbusch · Answer 2 · 2016-06-25T20:20:58.060

Two points :

The i = 2 must be outside the while loop.
Don't use Copy and PasteSpecial. Using the clipboard will give lots of problems later on. Additionally PasteSpecial likes you to be specific with "what" PasteSpecial action you're using. Rather assign values directly.

Dim i As Integer, Dataset As Variant

i = 2
Do While True
    If Cells(i, 1).Value <> "" And Not IsError(Cells(i, 2).Value) Then
        'This seems silly, but it overwrites the cell with its value.
        Dataset = Range(Cells(i, 1), Cells(i, 22)).Value
        Range(Cells(i, 1), Cells(i, 22)).Value = Dataset
        i = i + 1
    Else
        Exit Do
    End If
  Loop

Loop Crashing Excel VBA

2 Answers2