Help with a macro to increment row number please

[Steveoh]

Hi Guys,

I was wondering if someone could help with a macro im trying to create.
Basically the macro below asks the user for data, then copies that data into cell y1 and the time into z1.
Currently when the macro runs again it will copy and paste the new data in the same cells as before, overwriting what is there.

So i need the macro to increment the row number each time it is run, for the "time" and "from" columns.
into y2 z2
y3 z3 etc.

thanks for your help

#Sub add()
'
' addMacro
'
Range("g10") = Range("g10") + 1
    Dim from As String
    Dim too As String
 
    from = InputBox("Where From?")
    Range("y1").FormulaR1C1 = from
 
Range("z1").Value = Time
Range("z1").NumberFormat = "hh:mm:ss"

 

[Trebor76]

Hi Steveoh,

Try this:

Sub add()

    Dim sfrom As String
    Dim lOutputRow As Long
     
    sfrom = InputBox("Where From?")
    
    If Len(sfrom) = 0 Then
        Exit Sub
    End If
    
    If WorksheetFunction.CountA(ActiveSheet.Columns("Y:Z")) = 0 Then
        lOutputRow = 1
    Else
        lOutputRow = ActiveSheet.Range("Y:Z").Find("*", searchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    End If
    
    With ActiveSheet
        .Range("Y" & lOutputRow).Value = sfrom
        With .Range("Z" & lOutputRow)
            .Value = Time()
            .NumberFormat = "hh:mm:ss"
        End With
    End With

End Sub

Regards,

Robert

 

[alvinwlh]

hi

Sub add()
'
' addMacro
'
Range("g10") = Range("g10") + 1
    Dim from As String
    Dim too As String
    
    Last_row = Range("Y" & Rows.Count).End(xlUp).Row + 1
    from = InputBox("Where From?")
    Range("y" & Last_row).FormulaR1C1 = from
    
Range("z" & Last_row).Value = Time
Range("z" & Last_row).NumberFormat = "hh:mm:ss"
End Sub

 

[Steveoh]

hi

Sub add()
'
' addMacro
'
Range("g10") = Range("g10") + 1
    Dim from As String
    Dim too As String
 
    Last_row = Range("Y" & Rows.Count).End(xlUp).Row + 1
    from = InputBox("Where From?")
    Range("y" & Last_row).FormulaR1C1 = from
 
Range("z" & Last_row).Value = Time
Range("z" & Last_row).NumberFormat = "hh:mm:ss"
End Sub

Perfect! thank you very much