VBA Moving a cell one cell to the right

[alexfooty]

Hi
I have 21 columns of data C99:W99

I need a macro to move the orange cell (C100) one cell to the right each time the macro is run. When it eventually reaches cell W100 I need it to return to C100
Many thanks

 

[Peter_SSs]

Hope this is clearer.

I think so.
This also now uses columns E:Y as you requested in post #6

Sub Move_Cell_v2()
  Dim rFound As Range
  
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = RGB(255, 192, 0)
  Set rFound = Rows(100).Find(What:="", LookIn:=xlFormulas, SearchFormat:=True)
  If Not rFound Is Nothing Then
    Cells(81, rFound.Column).Resize(17).Value = Range("AC81:AC97").Value
    rFound.Cut Destination:=Cells(100, IIf(rFound.Column = 25, 5, rFound.Column + 1))
  End If
  Application.FindFormat.Clear
End Sub

 

[alexfooty]

Peter
Thank you. I called your macro from within a much larger macro that I use daily, and it works absolutely perfectly. Exactly what I wanted.
Thank you so much for all your help and patience.
Alex

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.

 

[alexfooty]

Peter
Any chance you could resend your code with comments explaining what each bit does? I'm trying to understand it but having difficulties.
It would help me greatly in trying to learn more about VBA.
Many thanks
Alex

 

[Peter_SSs]

Sure

Sub Move_Cell_v2()
  Dim rFound As Range
  
  'Clear any existing formatting settings for the Find process
  Application.FindFormat.Clear
  
  'Set the formatting that we want to look for (the orange cell)
  Application.FindFormat.Interior.Color = RGB(255, 192, 0)
  
  'Look in row 100 for the orange cell and set rFound to be that cell
  Set rFound = Rows(100).Find(What:="", LookIn:=xlFormulas, SearchFormat:=True)
  
  'This is just to stop the code erroring if there happens to be no orange cell in row 100
  If Not rFound Is Nothing Then
  
    'Starting at row 81 in the orange cell (rFound) column and for 17 cells from that one, transfer the values from column AC
    Cells(81, rFound.Column).Resize(17).Value = Range("AC81:AC97").Value
    
    'Cut the Orange cell and paste it 1 cell to the right (or to column E if we are at the end)
    rFound.Cut Destination:=Cells(100, IIf(rFound.Column = 25, 5, rFound.Column + 1))
    
  End If
  
  'Clear the orange formatting seeting from Find so that it doesn't impede any other Find operations that you might do
  Application.FindFormat.Clear
End Sub

 

[alexfooty]

Peter, thank you so much. This is so helpful for me in trying to come to terms with VBA.
You have been so helpful and it's greatly appreciated.
Alex

 

[Peter_SSs]

No problem.

 

[alexfooty]

Peter, this is the last time I promise!
I've been trying to adapt your code, using a colored cell again rgb(0, 176, 240), but this time copying & pasting values from two rows of data then moving the colored cell down two cells.
The change from rows to columns threw me I must admit and I got myself into a right mess. Have you got the patience to supply one last macro - Please?

I need to copy AN25:BK26 then paste the values into cells AN40:BK41 then the blue cell to move down two cells to AL42.
The next time the macro is run the vales will be pasted into AN42:BK43 etc

The last position for the blue cell will be AN66, after which it returns to cell AL40.

Thanks
Alex

 

[Peter_SSs]

copying & pasting values from two rows of data

From rows 25:26 to column AN beside the blue cell?

moving the colored cell down two cells.

The last position for the blue cell will be AN66, after which it returns to cell AL40.

If the blue cell is just moving down, how does it get from ALxx to ANxx?

 

[alexfooty]

Hope this is a bit clearer Peter

From rows 25:26 to column AN beside the blue cell?

From cells AN25:BK26 to cells AN40:BK41 beside the blue cell. Then move the blue cell down 2 cells to AL42 so that the next time the macro is run the vales will be pasted into AN42:BK43 etc

If the blue cell is just moving down, how does it get from ALxx to ANxx?

Sorry it's a typo. Should read - The last position for the blue cell will be AL66, after which it returns to cell AL40.