Offset Problem
I'm having a problem using offset with Worksheet_Change(). What I want to accomplish is when the person finishes entering a value in the current cell to move down one and to the left one. Right now I have my code in the Worksheet_Change event but this makes the active cell the next cell and then runs my code which offsets the cell to the wrong cell. All I want to do is make it easy for the user to enter data so that it basically acts as a carriage return. If you need more information let me know and I'll try to clarify better. Thanks, Chris ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Offset Problem
Thanks for the reply Chip, That would solve my problem if I had only one column but I have two columns and after they finish entering the value in the second column I want it to move to the first cell on the next row (Down one, Left one). Here is my code: Private Sub Worksheet_Change(ByVal Target As Range) Dim pvtTable As PivotTable Dim intColumn As String intColumn = Target.Column If intColumn = 2 Then ActiveCell.Offset(1, -1).Select End If Set pvtTable = Worksheets("Sheet1").Range("D2").PivotTable pvtTable.RefreshTable End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Offset Problem
I think I solved my problem by changing ActiveCell.Offset to Target.Offset Thanks for the help. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Offset Problem
select the area where you want to enter data and then use Tab instead of
enter. -- Regards, Tom Ogilvy "cford31" wrote in message ... Thanks for the reply Chip, That would solve my problem if I had only one column but I have two columns and after they finish entering the value in the second column I want it to move to the first cell on the next row (Down one, Left one). Here is my code: Private Sub Worksheet_Change(ByVal Target As Range) Dim pvtTable As PivotTable Dim intColumn As String intColumn = Target.Column If intColumn = 2 Then ActiveCell.Offset(1, -1).Select End If Set pvtTable = Worksheets("Sheet1").Range("D2").PivotTable pvtTable.RefreshTable End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
All times are GMT +1. The time now is 02:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com