How to loop through the same cell

Question

Private Sub CommandButton1_Click()
    Range("C10").Select

    For Range("C10").Value = 0 To 35
        Range("C10").Value = Range("C10").Value + 1
    Next
End Sub

I have written the above code for a cell to loop through the values = 0 to 35 . I am doing this because there are other formulas whose values change according to the value of Range("C10").

The code does not work

score 4 · Accepted Answer · edited Apr 22 '22 at 11:56

4

Add an increment variable to your For ... Next loop.

Option Explicit

Private Sub CommandButton1_Click()
    Dim i As Long

    For i = 0 To 35
        Range("C10").Value = i

        'do other stuff here
    
    Next
End Sub

edited Apr 22 '22 at 11:56

Dominique

16,450
15
56
112

answered Oct 29 '18 at 08:57

Dominique · Answer 2 · 2018-10-29T09:42:55.810

1

That's not a good way to do what you want:

You'd better use a counter variable, and do something like this:

for counter = 0 to 35:
  Range("C10").Value = counter
  // check the values of the things you want to verify,
  // e.g. by putting them in some cells for verification afterwards,
  // like "Range("D10").Offset(0,counter) = ...
Next

(This is just pseudo-code, there might be some syntax errors)

edited Oct 29 '18 at 09:42

answered Oct 29 '18 at 08:47

Dominique

16,450
15
56
112

score 0 · Answer 3 · answered Oct 29 '18 at 10:49

This one would loop from 0 to 35, changing the value of Range("A1") with i on every second. Looks animated:

Public Sub LoopMe()

    Dim i As Long
    For i = 0 To 35
        Worksheets(1).Range("A1") = i
        Application.Wait(Now + TimeValue("00:00:01"))
    Next

End Sub

How to pause for specific amount of time? (Excel/VBA)

How to loop through the same cell

3 Answers3