CODEWITHSUNDEEP
vbaexcelwhile-loopcrash
Kevin Le
Kevin Le

Reputation: 49

Loop Crashing Excel VBA

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?

Upvotes: 1

Views: 3530

Answers (2)

zatbusch
zatbusch

Reputation: 324

Two points :

  1. The i = 2 must be outside the while loop.
  2. 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

Upvotes: 0

Diogo
Diogo

Reputation: 132

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

Upvotes: 3

Related Questions