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]

Then hover over 'nr' and 'rFound.column'

??

 

[alexfooty]

'nr'=85, rFound.Column=40

What does '85' refer to?

 

[alexfooty]

Just noticed it is copying the data further down, starting at cell AN78

 

[Peter_SSs]

What does '85' refer to?

Next row - it is in the comments in the code

I don't know if you finished stepping right through the code but if you did, the value from the other sheet will have been copied to row 85.

I would now guess that there was other data or formulas below what you showed in post 26 but there was no way for us to know that.

So, if we were to copy to row 40 then 41 then 42 etc, where would we stop? If continuing we would eventually be over-writing that lower data.

 

[alexfooty]

The last row would be row 74 and yes there is data and formulas in rows 75, 76 & 77
I should have had the sense to mention that!
As I mentioned in my last post the data is being copied to cell AN78 onwards.

 

[alexfooty]

Peter, deleted data & formulas from rows 75,76 & 77 and everything works fine. The only problem being that if I move the green cell to SAT and run the macro, after SUN it then copies to MON but on the same row (row 40) until it gets to FRI then moves down one row.
That's not a major problem, I can live with that.

 

[alexfooty]

Peter, each day I run a macro that completes my workbook and prepares it for the following day. There was also some work I had to do by hand but thanks to your help everything is now integrated into the daily macro. I can't thank you enough, you've made my work so much easier. Many thanks.

 

[Peter_SSs]

there is data and formulas in rows 75, 76 & 77

The only problem being that if I move the green cell to SAT and run the macro, after SUN it then copies to MON but on the same row (row 40) until it gets to FRI then moves down one row.

I'm not too sure how much you are manually moving the green cell but see if this solves both of the above issues (with data still in rows 75 on if you want)

Sub Move_Cell_Across_v2()
  Dim rFound As Range
  Dim nr As Long
  
  'Clear any existing formatting settings for the Find process
  Application.FindFormat.Clear
  
  'Set the formatting that we want to look for
  Application.FindFormat.Interior.Color = RGB(112, 173, 71)
  
  'Look in row 39 for the green cell and set rFound to be that cell
  Set rFound = Rows(39).Find(What:="", LookIn:=xlFormulas, SearchFormat:=True)
  
  'This is just to stop the code erroring if there happens to be no green cell
  If Not rFound Is Nothing Then
    
    'Find the next row
    nr = Cells(75, 46).End(xlUp).Row + 1
    If nr <= rFound.Row Then nr = rFound.Row + 1
    
    'Copy the value from other sheet
    Cells(nr, rFound.Column).Value = Sheets("Daily Hour").Range("F6").Value
    
    'Cut the coloured cell and paste it to the next day
    rFound.Cut Destination:=rFound.Offset(, IIf(rFound.Offset(-1).Value = "SUN", -6, 1))
    
  End If
  
  'Clear the green formatting seeting from Find so that it doesn't impede any other Find operations that you might do
  Application.FindFormat.Clear
End Sub

 

[alexfooty]

That solves both problems perfectly and with conditional formatting as well. Thank you so much.

 

[Peter_SSs]

That solves both problems perfectly and with conditional formatting as well. Thank you so much.

You're welcome.