how to select specific cell to next one after the cell filled

0
votes

I would like to specify my excel to go on my cells. I mean when the first cell should be S5, and when this filled go to C6, then to C11, E11, G11, I11, K11, M11, O11, Q11, S11, then C12 E12........same method till s34 then goto H36.

I am try to use this:

If Not IsEmpty(Range("$C$11:$Q$11").Value) Then ActiveCell.Offset(0, 1).Select

Unfortunately in this case the active cell go one row down and one column right, and use not only in the specified range. I am not good in Excel macro.

3
excelvba
Think we might need more code. What do you mean by "special cells" and where are they defined? – SJR
Do you mean something along the lines of For each cl in Range("C6,C11,E11,G11,I11.....? – JvdV
Sorry no specail cells just these cells what I wrote. I would like to create a sequence where user can go on these cells (with barcode reader or caliper so put the data then enter) without click with mouse one by one. – Kornél Horváth
In that case you need to define them first and loop through them - see JvdV's comment. (And no need to select.) – SJR
Something like that can be achieved without VBA code. Unlock only those cells that can be selected, use sheet protection (disable selecting locked cells). If your scanner sends Enter you have to program it to send Tab or change excel options to go right after Enter. – BrakNicku

3 Answers

1
votes

Run this code and see what it does.

I'm not sure what exactly you are doing so this just shows the cell address as an illustration.

Sub x()

Dim cl As Range, r As Long

For r = 11 To 12
    For Each cl In Range("C" & r & ", E" & r & ",G" & r)
        MsgBox cl.Address
    Next cl
Next r

End Sub
0
votes

Take a look at the worksheet_change event. When you edit a cell you can then check which cell you are exiting from and force a move to the desired one.

0
votes

By and large, the code below does what you describe. Please install it on the code sheet of the worksheet on which you want the action (NOT a standard code module). The correct location is critical for its functioning.

Private Sub Worksheet_Activate()
    Cells(5, "S").Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    Dim C As Long
    Dim R As Long

    If Target.Cells.CountLarge > 1 Then Exit Sub

    Set Rng = Application.Union(Range("S5"), Range("C6"))
    For C = 5 To 19 Step 2
        Set Rng = Application.Union(Rng, Range(Cells(11, C), Cells(34, C)))
    Next C

    If Not Application.Intersect(Target, Rng) Is Nothing Then
        Select Case Target.Row
            Case 5
                Rng.Areas(2).Select
            Case 6
                With Rng.Areas(1)
                    If Len(.Value) = 0 Then
                        GoBack .Row, .Column
                    Else
                        Rng.Areas(3).Cells(1).Select
                    End If
                End With
            Case Else
                C = Rng.Areas.Count
                With Rng.Areas(C)
                    If Target.Address = .Cells(.Cells.Count).Address Then
                        Cells(36, "H").Select
                    Else
                        With Target
                            R = .Row
                            C = .Column + 2
                        End With
                        If C > .Column Then
                            R = R + 1
                            C = Rng.Areas(3).Column
                        End If
                        Cells(R, C).Select
                    End If
                End With
        End Select
    End If
End Sub

Private Sub GoBack(R As Long, _
                   C As Long)

    Dim Cell As Range

    Set Cell = Cells(R, C)
    MsgBox "Cell " & Cell.Address(0, 0) & " must be filled first.", _
           vbExclamation, "Missing data"
    Cell.Select
End Sub

I have programmed it so that S5 is selected whenever the sheet is activated. After the user makes a change to it C6 will be selected. If C6 is changed the code checks if S5 was filled and directs the user to go back if it's still blank. This method could be expanded to encompass a complete check if all cells must be filled. As the code is now the selection moves to the next cell when a change is made and to H36 after S34 was filled.