ActiveCell.Offset Code

[workerboy]

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

[Jim Thomlinson]

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
>
>

[workerboy]

Hi Jim,

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