Results 1 to 8 of 8

Thread: IF function with cursor move

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF function with cursor move

    I am using a hand scanner to scan the word "OK" or "NOT OK" into the D column of a table. There are 2 more columns in this table to be used only if the D column is not ok.

    If I scan "OK" I want the cursor to move to the next row column A.

    So, if everything is "OK" I want the cursor to start over at the next open row in column A.

    this spreadsheet is to only by filled in by scanning. I don't want any interaction from the operators, so I am trying to make this automatic.

    I am a novice to coding. I have tried so many different things and none have worked. That is why I a not posting any code for this question.

    Please HELP!!! I am so confused.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,233
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    14 Thread(s)

    Default Re: IF function with cursor move

    Welcome to the forum!

    Put the following macro in sheet events

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Count > 1 Then Exit Sub
      If Not Intersect(Target, Range("D:D")) Is Nothing Then
        If UCase(Target.Value) = "OK" Then
          Range("A" & Target.Row + 1).Select
        End If
      End If
    End Sub
    SHEET EVENT
    Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: IF function with cursor move

    Quote Originally Posted by DanteAmor View Post
    Welcome to the forum!

    Put the following macro in sheet events

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Count > 1 Then Exit Sub
      If Not Intersect(Target, Range("D:D")) Is Nothing Then
        If UCase(Target.Value) = "OK" Then
          Range("A" & Target.Row + 1).Select
        End If
      End If
    End Sub
    SHEET EVENT
    Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
    That worked!! Thank you so much!!

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,233
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    14 Thread(s)

    Default Re: IF function with cursor move

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF function with cursor move

    Dante, this worked like a charm... for that function! But now I have 2 Private Sub Worksheet_Change functions and one has stopped working.

    The one you gave me and one for an auto time & date stamp:

    If Target.Cell.Count >1 Then Exit Sub
    If Not Intersect(Target, range("C3:C8931")) Is Nothing Then With Target (1, 6)
    .Value = Date & " " & Time
    .EntireColumn.AutoFit
    End With
    End If
    End Sub


    I don't know how to add the two together. Can you help?

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,233
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    14 Thread(s)

    Default Re: IF function with cursor move

    Try this

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Count > 1 Then Exit Sub
      'select next row
      If Not Intersect(Target, Range("D:D")) Is Nothing Then
        If UCase(Target.Value) = "OK" Then
          Range("A" & Target.Row + 1).Select
        End If
      End If
      'auto time & date stamp
      If Not Intersect(Target, Range("C3:C8931")) Is Nothing Then
        With Target(1, "F")
          .Value = Date & " " & Time
          .EntireColumn.AutoFit
        End With
      End If
    End Sub
    Regards Dante Amor

  7. #7
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF function with cursor move

    Dante, I so appreciate you helping me!! Now, neither of the codes do what is expected.

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,233
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    14 Thread(s)

    Default Re: IF function with cursor move

    Quote Originally Posted by frogcon View Post
    Dante, I so appreciate you helping me!! Now, neither of the codes do what is expected.
    Run this macro:

    Code:
    Sub en()
    Application.EnableEvents = True
    End Sub
    Last edited by DanteAmor; Sep 23rd, 2019 at 02:15 PM.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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