I'm trying to make value's cycle with VBA (or another way).
The idea is that when the button is pressed that all values go forward, and the first one is moved to the back.
The cells with a value are C4, F4, I4, L4, O4, R4, U4, X4, AA4 and AD4 (always 2 cells in between.)
Also, not all 10 cells are always in use, sometimes there are only 2 or three, but other times you also have 7 or up to all 10.
Values are inputted the first time with a drop-down menu.
This is the macro I tried to use, this one just gives error 1004 :
Sub cycle()
Range("C4").Select
Selection.Copy
Range("AG15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F4:AD4").Select
Application.CutCopyMode = False
Selection.Copy
Range("C4").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("AG15").Select
Selection.Copy
Sheets("Blad1").Select
Range("C5").Select
Range(Selection, Selection.End(xlRight)).Select
Selection.Copy
Range("AG15").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub
Added a screenshot as example with 4 values. Just keep in mind this can be up to 10 values.
I already tried to just copy the value of C4 to another cell, then select F4 till AD4, copy them end paste to C4, and then copy the one that was set aside, back to the first available of those 10 cells, starting from the left, but it messed up the whole excel, so deleted that one.
CodePudding user response:
Cycle Range Values
Option Explicit
Sub CycleLeft()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Sheets("Blad1")
Dim rg As Range: Set rg = ws.Range("C4,F4,I4,L4,O4,R4,U4,X4,AA4,AD4")
Dim currentCell As Range, previousCell As Range, CurrentValue, FirstValue
Dim IsNotFirst As Boolean
For Each currentCell In rg.Cells
CurrentValue = currentCell.Value
If Len(CStr(CurrentValue)) > 0 Then ' is not blank
If IsNotFirst Then
previousCell.Value = CurrentValue
Else
FirstValue = CurrentValue
IsNotFirst = True
End If
Set previousCell = currentCell
End If
Next currentCell
If Not previousCell Is Nothing Then previousCell.Value = FirstValue
End Sub