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


--
saziz
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=503019

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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=503019

Thank you Leith. The fit in perfectly.
Syed Aziz


--
saziz
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=503019

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 he

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
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=503019

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 he

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
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=503019

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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=503019