How to advance one column to right?

[saziz]

Hi All,
In my code I have chosen the last row in col A as cut it. Now I want to advance one column in the same row line and paste this value.

Here is my code:

lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(lRow + 0, 1).Select
Application.CutCopyMode = False
Selection.Cut
i want to go one col to right in the same row and paste it. I am trying it for long but cannot get it to work.

Appreciate your help.
Thank you
Syed Aziz

[Leith Ross]

Hello Syed,

You don't need to to use Cut/Copy/Paste to copy the contents of a cell into another cell. It looks like you have this code in the body of a with statement, so I didn't make any changes to the periods you have placed in the code.

Example:
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(lRow, 1).Offset(0, 1).Value = .Cells(lRow, 1).Value

The Offset adds the values to the cells's Row and Column numbers to create the new cell address. For the Row negative numbers more Left and positive move Right. For the Column negative numbers move Up and positive move Down.

Sincerely,
Leith Ross

[saziz]

Thank you Leith. The fit in perfectly.
Syed Aziz

[saziz]

Hi Leith/All
Another Q.
I am trying to put the calculated value in a message box for the user. It seems I am missing something here:

lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(lRow + 1, 1).FormulaR1C1 = "=average(r2c:r[-1]c)"
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(lRow, 1).Offset(0, 1).Value = .Cells(lRow, 1).Value

msgbox "Your Ave is " & lRow.value <-- here is the problem

But it gives me an error saying it does not support~
If I take out .value it give me row no. not the value.

Appreciate your help.
Thanks
Syed

[saziz]

Hi Leith/All
Another Q.
I am trying to put the calculated value in a message box for the user. It seems I am missing something here:

lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(lRow + 1, 1).FormulaR1C1 = "=average(r2c:r[-1]c)"
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(lRow, 1).Offset(0, 1).Value = .Cells(lRow, 1).Value

msgbox "Your Ave is " & lRow.value <-- here is the problem

But it gives me an error saying it does not support~
If I take out .value it give me row no. not the value.

Appreciate your help.
Thanks
Syed

[Leith Ross]

Hello Syed,

lRow is is a variable that holds the last row number. You get an error because you are trying to access it as a Range object. Add another variable to hold the average result. The message box can then refer to that variable to display the answer.

Example:

Dim Answer
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(lRow + 1, 1).FormulaR1C1 = "=average(r2c:r[-1]c)"
Answer = .Cells(lRow + 1, 1).Value
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(lRow, 1).Offset(0, 1).Value = .Cells(lRow, 1).Value

msgbox "Your Ave is " & Answer

Sincerely,
Leith Ross