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
– Skaterhaz, Commented Jun 24, 2016 at 14:44
Your loop is exiting when true, so I'd suggest that the criteria are never being met. — Nathan_Sav
– Nathan_Sav, Commented Jun 24, 2016 at 14:44

Diogo · Accepted Answer · 2016-06-24 14:44:28Z

3

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

answered Jun 24, 2016 at 14:44

Diogo

1325 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zatbusch · Accepted Answer · 2016-06-25 20:20:58Z

0

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

edited Jun 25, 2016 at 20:20

answered Jun 25, 2016 at 6:07

zatbusch

3242 silver badges10 bronze badges

Collectives™ on Stack Overflow

Loop Crashing Excel VBA

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related