ActiveCell.Offset Code
 

Hi

I'm trying my hand at some VBA coding in excel and was stuck on one
part if someone could please help me out ... here is part of the code:

i = Range("B2") + 1 // assign a variable i, the value of the number
in B2+1
Range("B2") = i // cell B2 displays the new value of i



Range("B2").Select
i = ActiveCell.Offset((y - 1), 0).Select + 1 // in this case y
is a variable row number, i want to be able to do the same as above,
but for the row number as input by the user
ActiveCell.Offset((y - 1), 0) = i // I would also like to be
able to display that corresponding cell as above

If you need more of the code, to understand my problem, please let me
know as well as if you need some more explanation as to what it is i am
trying to do in this case.

Thanks


Here is a copy of all the code to that point, with the same code as
above in red:

Dim i As Integer
Dim x As String

y = InputBox("Enter Row No", "Row No.")

If (y = "") Then
MsgBox "Stop. Try Again", vbOKCancel

Else

If (y = 1) Then

i = Range("B2") + 1
Range("B2") = i
Range("C2:G2").Select
Selection.Copy
Sheets("Sheet2 (2)").Select
Range("B2").Select
ActiveCell.Offset(0, (i - 1) * 5).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.CutCopyMode = False

Else

Range("B2").Select
i = ActiveCell.Offset((y - 1), 0).Select + 1
ActiveCell.Offset((y - 1), 0) = i


--
workerboy
------------------------------------------------------------------------
workerboy's Profile: http://www.excelforum.com/member.php...o&userid=34121
View this thread: http://www.excelforum.com/showthread...hreadid=570081

ActiveCell.Offset Code
 

Give this a try...

Dim i As Integer
Dim x As String

y = InputBox("Enter Row No", "Row No.")

If (y = "") Then
MsgBox "Stop. Try Again", vbOKCancel

Else

If (y = 1) Then

i = Range("B2") + 1
Range("B2") = i
Range("C2:G2").Copy Destination:=Sheets("Sheet2 (2)").Range("B2").Offset(0,
(i - 1) * 5)
Sheets("Sheet1").Select 'may not need
'Application.CutCopyMode = False 'Don't Need

Else

Range("B2").Select 'May not need
i = Range("B2").Offset((y - 1), 0).Value + 1
Range("B2").Offset((y - 1), 0) = i

--
HTH...

Jim Thomlinson


"workerboy" wrote:


Hi

I'm trying my hand at some VBA coding in excel and was stuck on one
part if someone could please help me out ... here is part of the code:

i = Range("B2") + 1 // assign a variable i, the value of the number
in B2+1
Range("B2") = i // cell B2 displays the new value of i



Range("B2").Select
i = ActiveCell.Offset((y - 1), 0).Select + 1 // in this case y
is a variable row number, i want to be able to do the same as above,
but for the row number as input by the user
ActiveCell.Offset((y - 1), 0) = i // I would also like to be
able to display that corresponding cell as above

If you need more of the code, to understand my problem, please let me
know as well as if you need some more explanation as to what it is i am
trying to do in this case.

Thanks


Here is a copy of all the code to that point, with the same code as
above in red:

Dim i As Integer
Dim x As String

y = InputBox("Enter Row No", "Row No.")

If (y = "") Then
MsgBox "Stop. Try Again", vbOKCancel

Else

If (y = 1) Then

i = Range("B2") + 1
Range("B2") = i
Range("C2:G2").Select
Selection.Copy
Sheets("Sheet2 (2)").Select
Range("B2").Select
ActiveCell.Offset(0, (i - 1) * 5).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.CutCopyMode = False

Else

Range("B2").Select
i = ActiveCell.Offset((y - 1), 0).Select + 1
ActiveCell.Offset((y - 1), 0) = i


--
workerboy
------------------------------------------------------------------------
workerboy's Profile: http://www.excelforum.com/member.php...o&userid=34121
View this thread: http://www.excelforum.com/showthread...hreadid=570081

ActiveCell.Offset Code
 

Hi Jim,

Thanks a lot, that code really helped, plus it also helped in cleaning
out my code, making it shorter too.


--
workerboy
------------------------------------------------------------------------
workerboy's Profile: http://www.excelforum.com/member.php...o&userid=34121
View this thread: http://www.excelforum.com/showthread...hreadid=570081