Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
ActiveCell Offset | Excel Programming | |||
Activecell Offset | Excel Programming | |||
ActiveCell.Offset w/ VBA | Excel Programming | |||
activecell offset | Excel Programming |