Loop that will count rows

Question

I am trying to write a loop that will get my code to take a number entered by a user in cell "C4" and have that value pasted in Cell "C10". Any new number entered by the user should also be pasted under cell "C10". I am stuck down at the bottom of this code at "For i =.." Any help is greatly appreciated

Public Sub Sheet1()

     Application.OnKey "{ENTER}", "EnterANumber"

End Sub

Sub EnterANumber()

If Range("C4").Value < 10 Then

MsgBox ("The number you entered is less than 10")


    Else

    If Range("C4").Value > 10 Then

    MsgBox ("The number you entered is greater than 10")

            Else

            If Range("C4").Value = 10 Then

            MsgBox ("The number you entered is 10!")

        End If


   End If


End If

'copy the number from cell c4 and place it into cell c10

Range("C4").Select

    Selection.Copy

        Range("C10").Select

            ActiveSheet.Paste

'copy the number from C10 and place it in C11. Any new number after that place in C12,C13,C14,etc.

For i = 1 To 100

  Selection.Copy

    ActiveCell.Offset(1, 0).Select

    ActiveSheet.Paste
Next i


End Sub

1) http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros 2) http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba — Dmitry Pavliv, Aug 15 '14 at 18:56

IAmDranged · Answer 1 · 2014-08-16T01:27:51.397

Here is an alternative using a worksheet event procedure that would look more convenient than using the onKey event - I have just skipped the message box parts. If you give this a try, don't forget to put it in your worksheet code module.

Note this code makes use of the unloved End() method of the Range object - which seems appropriate in this context though.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim outputCell As Range

    If Not intersect(Target, Range("C4")) is Nothing Then

        With Range("C10")

            If IsEmpty(.Value) Or IsEmpty(.Offset(1, 0).Value) Then
                Set outputCell = IIf(IsEmpty(.Value), .Item(1), .Offset(1, 0).Item(1))
            Else
                Set outputCell = .End(xlDown).Offset(1, 0)
            End If

        End With

        outputCell.Value = Range("C4").Value

    End If

End Sub

score -1 · Answer 2 · answered Aug 15 '14 at 20:31

I modified your code as follows:

Public Sub Sheet1()
     Application.OnKey "{ENTER}", "EnterANumber"
End Sub

Sub EnterANumber()

    If Range("C4").Value < 10 Then
        MsgBox ("The number you entered is less than 10")
    End If

    If Range("C4").Value > 10 Then
        MsgBox ("The number you entered is greater than 10")
    End If

    If Range("C4").Value = 10 Then
        MsgBox ("The number you entered is 10!")
    End If

    If Range("C10").Value = "" Then
        RowNo = 10
    Else
        RowNo = Range("C10").CurrentRegion.Rows.Count + Range("C10").Row
    End If

    Range(Cells(RowNo, 3), Cells(RowNo, 3)).Value = Range("C4").Value

End Sub

Loop that will count rows

2 Answers2