Offset Problem

  • Thread starter Thread starter cford31
  • Start date Start date
C

cford31

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

If I understand your question correctly, all you need to do go to the Tools
menu, choose Options then the Edit tab, and check the "Move selection after
Enter" setting, and set the direction setting to down. You can set this
with VBA with code like

Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlDown


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
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
 
I think I solved my problem by changing ActiveCell.Offset to
Target.Offset

Thanks for the help.
 
Back
Top