-1

Newbie to Excel VBA here.

With the following code:

Dim I As Integer

I = 1

Do
    If (Cells(I, "A").Value = "Potatoes") Then

        MsgBox ("Potatoes! I found a Potato in row " & Str(I))
    End If
    I = I + 1

Loop While (Cells(I, "A").Value <> " ")

End Sub

After MsgBox gives the message "Potatoes! I found a Potato in row 3" "Potatoes! I found a Potato in row 17"

I keep getting runtime error 6, Overflow.

The same applies to the alternative code

Public Sub Do_Loop_Exercise1()

Dim I As Integer
Do
    If (Cells(I, "A").Value = "Potatoes") Then

        MsgBox ("Potatoes! I found a Potato in row " & Str(I))
    End If
    I = I + 1

Loop Until (Cells(I, "A").Value = " ")

End Sub

I tried changing data type of I to Long and instead got error '1004'; Application Defined or object defined error.

I have resolved to use the For/Next loop structure where ever possible in future but want to understand why the above 2 codes wont run without throwing up the runtime 6 error message or in the alternative (when changing I to Long), error 1004. Any insight will be appreciated.

Foxfire And Burns And Burns
  • 8,150
  • 2
  • 18
  • 36
worded
  • 1
  • 2
  • 1
    You're not getting out of the loop. With `integer` your code breaks on trying to assign `32768` to `I` (https://stackoverflow.com/questions/48523860/vba-code-hanging-at-row-32767). When you change to `Long`, you go on longer and run into an error trying to access `A1048576 + 1`, a cell that doesn't exist. My guess: you want to change `" "` to `""`; loop will end on first empty cell. Avoid problems with a `For/Next` loop. This may be helpful: https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba. – ouroboros1 May 05 '22 at 08:39
  • @ ouroboros1 Thanks for the input. But changing " " to "" doesn't resolve the problem. Rather MsgBox doesn't populate at all . As for your advise to Avoid problems with For/Next loop, will go through the link provided. Thanks – worded May 05 '22 at 10:10
  • If "MsgBox doesn't populate at all", this probably means that you are reaching your exit condition before reaching a cell that contains "Potatoes". E.g. if "A2" is empty, you will exit after your first iteration, since `Cells(2, "A").Value <> "" = False`. Maybe step through your code with `F8` and use `Debug.Print` to see where your code is doing something you're not expecting: e.g. after `Do` use `Debug.Print (Cells(I, "A").Address & " has value: " & Cells(I, "A").Value)` – ouroboros1 May 05 '22 at 10:48

0 Answers0