4

I'm trying to move one cell to the right in a macro. 

Range("C5").Select  <-- a formula that needs to be copied
Selection.Copy
Range("B5").Select
Selection.End(xlDown).Select
SendKeys ("{TAB}")
Range(Selection, Selection.End(xlUp)).Select
Selection.Paste

SendKeys does not appear to be the answer to this issue as it puts a TAB or RIGHT arrow directly into the next line of macro code, emulating keyboard strokes only. Is there another way to move my cell selection to the right from the end of Bn to Cn, where n is unknown?

underscore_d
  • 6,309
  • 3
  • 38
  • 64
Mark Spain
  • 83
  • 1
  • 2
  • 7
  • 1
    Sendkeys is never the solution as it opens a huge can of worms where anything can go wrong. Look at Range.Offset https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-offset-property-excel – Sorceri Apr 05 '18 at 15:47
  • 1
    @JeanRostan did you intend to put a `+1` in there? – ashleedawg Apr 05 '18 at 15:51
  • Do you want to move the content of a cell , to the cell right next to it ? Your code is kinda messy. If I understand correctly, that would be simply to get active cell, then add +1 to the column. Use `cellA = ActiveCell` and then `cellRight = Cells(cellA.Row,cellA.Column+1)` and `cellRight.Value=cellA.Value` thanks@ashleedawg – Jean Rostan Apr 05 '18 at 15:53
  • Thank you Sorceri. This is exactly what was needed. – Mark Spain Apr 05 '18 at 15:54

4 Answers4

6

To reference one cell to the right of the active cell:

ActiveCell.Offset(0, 1)

To return the address of the cell one to the right:

MsgBox ActiveCell.Offset(0, 1).Address

To move the selection once cell to the right:

ActiveCell.Offset(0, 1).Select

Code from your answer but tidied up:

Range("C5").Copy
Range("B5").End(xlDown).Offset(columnoffset:=1).Activate
Range(Selection, Selection.End(xlUp)).Select

It doesn't really do anything. It copies but doesn't paste.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
3

your code can boil down to:

range("C5").Copy range("B5").End(xlDown).Offset(columnoffset:=1)

or, if you only need to paste values:

range("B5").End(xlDown).Offset(columnoffset:=1).value = range("C5").value
DisplayName
  • 13,283
  • 2
  • 11
  • 19
2

I think you are after something like the code below, explanations inside the code's comments:

Dim LastRow As Long
Dim LastCellColC As Range

' find last row with data in column "B"
LastRow = Cells(Rows.Count, "B").End(xlUp).Row

Set LastCellColC = Range("B" & LastRow).Offset(0, 1)

' if you realy need to use Select
LastCellColC.Select

I think this is what you are really after, copying the formula from "C5" till the last cell in Column "C" (according to the last row with data in column "B")

Dim LastRow As Long

' find last row with data in column "B"
LastRow = Cells(Rows.Count, "B").End(xlUp).Row

' copying the formula from "C5" all the way to the last cell
Range("C5").AutoFill Destination:=Range("C5:C" & LastRow), Type:=xlFillDefault
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
1

This was the fix to my issue. 

Range("C5").Select
Selection.Copy
Range("B5").Select
Selection.End(xlDown).Select
ActiveCell.Offset(columnoffset:=1).Activate
Range(Selection, Selection.End(xlUp)).Select
Mark Spain
  • 83
  • 1
  • 2
  • 7