Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Offset cell (Excel 2000)

    Hi all,

    This works for me, but it doesn't, what I need to do is Offset 1 column after a worksheet change but can't figure out a way to do it, without this lengthy code. I tried in the second procedure to achieve it, but couldn't.

    Can anyone see what I have done wrong in the second sub, or do I need to take a different approach?

    Thanks,
    Darryl.


    Private Sub worksheet_Change(ByVal Target As Range) On Error Resume Next
    If Not Intersect(Target, Range("A7")) Is Nothing Then
    If Range("A7") > 0 Then Range("B7").Select
    End If
    If Not Intersect(Target, Range("B7")) Is Nothing Then
    If Range("B7") > 0 Then Range("C7").Select
    End If
    If Not Intersect(Target, Range("C7")) Is Nothing Then
    If Range("C7") > 0 Then Range("D7").Select
    End If
    If Not Intersect(Target, Range("D7")) Is Nothing Then
    If Range("D7") > 0 Then Range("E7").Select
    End If
    If Not Intersect(Target, Range("E7")) Is Nothing Then
    If Range("E7") > 0 Then Range("F7").Select
    End If
    If Not Intersect(Target, Range("F7")) Is Nothing Then
    If Range("F7") > 0 Then Range("G7").Select
    End If
    If Not Intersect(Target, Range("G7")) Is Nothing Then
    If Range("G7") > 0 Then Range("H7").Select
    End If
    If Not Intersect(Target, Range("H7")) Is Nothing Then
    If Range("H7") > 0 Then Range("I7").Select
    End If
    If Not Intersect(Target, Range("I7")) Is Nothing Then
    If Range("I7") > 0 Then Range("J7").Select
    End If
    If Not Intersect(Target, Range("J:J")) Is Nothing Then
    Cells(Target.Row + 1, 1).Select
    End If
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range) 'second sub


    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Cells(Target.Column + 1, 1).Select
    End If
    If Not Intersect(Target, Range("J:J")) Is Nothing Then
    Cells(Target.Row + 1, 1).Select
    End If

    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Offset cell (Excel 2000)

    I am not sure what you want exactly, could you elaborate? When do you want to offset 1 column: any change, changes in particular cells?
    [Note: Tools - options - edit tab and change the direction after Enter is more efficient than any VB code if you want to always do it]

    You could modify the code that was in <post#=450598>post 450598</post#>, but change the column instead of the row:
    Cells(0,Target.Column+ 1).Select

    And change the desired "intersection range"

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset cell (Excel 2000)

    It is possible to change several cells in one fell swoop, for example by selecting a range, entering a value or formula and pressing Ctrl+Enter. So the Target argument of Worksheet_Change is not necessarily a single cell. You will have to indicate precisely what you want to happen then - do you want to select the cell next to the first changed cell, or ...?

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Offset cell (Excel 2000)

    Hans, what I would like to do is Start in A column, then offset it 10 times on cell input, then on the 10th input go back to column A., and repeat that 10 times.
    The code i have now I would need about 400 lines of code to acomplish that. The reason for staying in a 10 X 10 map is to have a reading device input directly into a cell then once the cell is populated move on to the next.

    Thanks,
    Darryl.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset cell (Excel 2000)

    Did you try Steve's suggestion?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Offset cell (Excel 2000)

    <P ID="edit" class=small>(Edited by sdckapr on 16-Feb-05 12:25. Fixed error)</P>Are you looking for this?

    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:I")) Is Nothing Then
    Cells(Target.Row, Target.Column + 1).Select
    End If
    If Not Intersect(Target, Range("J:J")) Is Nothing Then
    Cells(Target.Row + 1, 1).Select
    End If
    End Sub</pre>


    Steve

  7. #7
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Offset cell (Excel 2000)

    Thanks Steve

    I had everythng right except for the ("A:i")---I had ("A:A")

    Works great.

    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •