How to write each inputbox entry into row one down in Excel?

Question

I wrote an Excel macro and it seems to work fine. It displays an inputbox and once I give the value in it. It saves that value into first cell of column C (C1). However the second time I run macro I want it to be written into C2 and keep all datas in different rows in column C but each time, it writes it into C1 and cause a data loss.

Sub DataInput()

    Dim SearchTarget As String
    Dim myRow As Long
    Dim Rng As Range


    Static PrevCell As Range
    Dim FoundCell As Range
    Dim CurCell As Range
    Dim a As String
    Dim Target As Range

    'SearchTarget = "asdf"
    SearchTarget = InputBox("Scan or type product barcode...", "New State Entry")

    If PrevCell Is Nothing Then
        myRow = Selection.Row
        Set PrevCell = Range("C" & myRow)
    End If

    'Set Rng = Range("C:C,E:E") 'Columns for search defined here
    Set Rng = Range("C:C,C:C") 'Columns for search defined here

    With Rng
        Set FoundCell = .Cells.Find(What:=SearchTarget, _
        After:=PrevCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True)
    End With

    If FoundCell Is Nothing Then

        MsgBox SearchTarget & " was not found."
        Range("C1").Value = InputBox("code?")
        Range("D1").Value = Now()

    Else

        FoundCell.Activate
        '        If PrevCell.Address = FoundCell.Address Then
        '            MsgBox "there's only one!"
        '        End If

        ActiveCell.Offset(0, 1).Select
        timestamp = Format(Now(), "dd-mmm-yy hh:mm")
        ActiveCell = timestamp
        ActiveCell = Now()

        ActiveCell.Offset(0, 2).Select
        ActiveCell = "T141000"

        ActiveCell.Offset(0, 1).Select

        Set PrevCell = FoundCell

    End If

End Sub

Answer 1

The problem here lies in your if statement - you are always storing the newly entered codes in cells C1 and the date in D1. You need to dynamically work out the next available row number and use that instead. Try something like this:

Public Sub DataInput()

    ...

    If FoundCell Is Nothing Then

        MsgBox SearchTarget & " was not found."

        Dim nextFreeRow As Integer
        nextFreeRow = Range("C" & Rows.Count).End(xlUp).Row + 1

        Range("C" & nextFreeRow).Value = InputBox("code?")
        Range("D" & nextFreeRow).Value = Now()

    Else
        ...
    End If

    ...

End Sub