Help with a macro to increment row number please

Steveoh

New Member
Joined
Nov 16, 2011
Messages
4
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


Code:
#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"
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
Hi Steveoh,

Try this:

Code:
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

Active Member
Joined
Feb 16, 2009
Messages
305
hi

Code:
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

New Member
Joined
Nov 16, 2011
Messages
4
hi

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top