code for moving to the first blank cell in a worksheet; moving left,right,up or down

[EvaGordon]

I have created an application form as a userform in Excel. I need to send the data entered into my text boxes when the program runs to separate excel cells in a worksheet.

My problem is I can use code that will send the textbox entry to a specific cell eg. If my textbox is named Surname I can say

range("a5").value = surname but if I do that when I run the programme to enter the next record for someone else the first record will be overwritten.

I NEED A CODE LINE THAT WILL TAKE ME TO THE FIRST EMPTY CELL AND ALLOW ME TO MOVE DOWN OR TO THE LEFT OR TO THE RIGHT

[Mortivan]

Try this:

Option Explicit

Const conLEFT = 0
Const conRIGHT = 1
Const conUP = 2
Const conDOWN = 3

Sub NextCell(iDirection As Integer)

'Call this sub to move active cell 1 space.
'iDirection: 0-Left, 1-Right, 2-Up, 3-Down.
 
On Error GoTo ehOffSheet

With ActiveWindow.ActiveCell
    Select Case iDirection
    Case conLEFT
        ActiveSheet.Cells(.Row, .Column - 1).Activate
    Case conRIGHT
        ActiveSheet.Cells(.Row, .Column + 1).Activate
    Case conUP
        ActiveSheet.Cells(.Row - 1, .Column).Activate
    Case conDOWN
        ActiveSheet.Cells(.Row + 1, .Column).Activate
    End Select
End With

exNextCell:

Exit Sub


ehOffSheet:

MsgBox "Attempt to move off sheet!", vbExclamation
Resume exNextCell

End Sub

-mort

[Mortivan]

To move to the next blank cell,

Option Explicit

Const conLEFT = 0
Const conRIGHT = 1
Const conUP = 2
Const conDOWN = 3

Sub NextCell(iDirection As Integer)

Dim lActiveRow As Long
Dim lActiveCol As Long

With ActiveWindow.ActiveCell
    lActiveRow = .Row
    lActiveCol = .Column
End With

On Error GoTo ehOffSheet

Do
    With ActiveWindow.ActiveCell
        Select Case iDirection
        Case conLEFT
            ActiveSheet.Cells(.Row, .Column - 1).Activate
        Case conRIGHT
            ActiveSheet.Cells(.Row, .Column + 1).Activate
        Case conUP
            ActiveSheet.Cells(.Row - 1, .Column).Activate
        Case conDOWN
            ActiveSheet.Cells(.Row + 1, .Column).Activate
        End Select
    End With
Loop Until ActiveWindow.ActiveCell.Text = ""


exNextCell:

Exit Sub


ehOffSheet:

MsgBox "Attempt to move off sheet!", vbExclamation
ActiveSheet.Cells(lActiveRow, lActiveCol).Activate

Resume exNextCell

End Sub

-mort