Creating a loop using a list of values with Excel Macro/VBA

[bobexcel1]

I have a list of values in cells A1:A10.

I want to create a macro so that cell B1 will first contain the first value in the list, then the second value replace it, then the third, and so on. Once the entire list is run through, it should loop back to the first value again. The same sequence/order should run throughout.

If it impacts any of the code, I will likely end up using a button to run this code, so that each time the button is pressed the next item on the list takes over cell B1.

Is there any code that can be entered to help start the loop? When I run a macro all I get the same value repeatedly.

Thanks for any help!

 

[Misca]

Welcome to the message board!

Here's a simple macro that loops the values i times:

Sub LoopValues()

Dim i As Integer
Dim c As Range
Dim d As Range
Dim Rng As Range


With ActiveSheet
'Setting the ranges:
    Set Rng = .Range("A1:A10")
    Set d = .Range("B1")
    
    For i = 1 To 10 'How many times you want to loop all the values?
        
    'Looping each cell in range Rng:
        For Each c In Rng
            d.Value = c.Value   'Changes the value in range d
            
            'Your code here: What should happen before the next value gets added
            
        Next c
        
    Next i
    
End With


End Sub

The code runs really fast. You might want to add something to the code after the values have been changed.

I don't know how do you want to fire the macro: A button or a keyboard shortcut are both safe ways where as event triggered macros are a lot trickier.

 

[Misca]

I read your instructions again and understood you don't want the macro to loop the values over and over again automatically but to change them one at a time every time you fire your macro.

Place the following code to an empty Module. I changed the i to a public variable so it gets stored in memory even after the macro has run.

Public i As Integer


Sub LoopValuesOneByOne()


Dim c As Range
Dim d As Range
Dim Rng As Range




With ActiveSheet
'Setting the ranges:
    Set Rng = .Range("A1:A10")
    Set d = .Range("B1")
    
    i = i + 1   'Increments the i
    
    If i > Rng.Cells.Count Then i = 1
            
    'Looping cells in range Rng:
        
    d.Value = Rng.Cells(i).Value   'Changes the value in range d
            
    
End With


End Sub

Another way to make the macro remember the i after the macro has run would be to store the i value in a cell. This way you could continue where you were even if you closed the workbook.