Macro code to set range

[muddan madhu]

Hi,

I have a macro to calculate the time ...
Col A - Start time
Col B - End Time

similarly, I have Col C , Col D , Col E , Col F......... ( Start Time
& End Time)

I have Created Buttons ( in top row Button 1, Button 2 , Button 3 )

Button 1 - for Col A , B
Button 2 - for Col C, D
Button 3 - for Col E, F

If user click that buttton it gives the system time as 00:06:53 in A5
(hh:mm:ss) and again
if user click that button it will gives the system 00:06:55 in B5 &
moves to A6 cell.
if user click that button it gives the time in A6 and Moves to B6.
similarly it moves down as and when user clicks..

What i need is - If the user clicks button 1, the activecell should be
A5 and it moves on.
If user click button 3, the activecell should be E5.

Please Check code and test it and advice if you there is better option
rather than this ?

Here is my code :


Sub macro1()

ActiveCell.Select
If ActiveCell = "" Then
ActiveCell.FormulaR1C1 = "=now()-Today()"
Selection.Copy
'ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Else
If ActiveCell <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=now()-Today()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

If ActiveCell > 0 Then
ActiveCell.Offset(1, -1).Select

End If
End If
End If
End Sub

 

[Bernie Deitrick]

Muddan ,

Assign the macro below to all three buttons - change the "Button 1" "Button 2" and "Button 3" to the
text that you have applied to the buttons. I've assumed that you are using the button from the
Forms commandbar, not the Controls commandbar.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim myR As Long
Dim myC As Integer

If Application.Caller = "Button 1" Then
myC = 1
ElseIf Application.Caller = "Button 2" Then
myC = 3
ElseIf Application.Caller = "Button 3" Then
myC = 5
End If

myR = Cells(Rows.Count, myC).End(xlUp).Row

If Cells(myR, myC + 1).Value = "" Then
With Cells(myR, myC + 1)
.Value = Time()
.NumberFormat = "h:mm:ss AM/PM"
End With
Else
With Cells(myR + 1, myC)
.Value = Time()
.NumberFormat = "h:mm:ss AM/PM"
End With
End If

End Sub

 

[muddan madhu]

Bernie,

Thank u very much, it works great.

I need small help, Please help me.

I need to learn advance coding in Excel VBA. Can u guide me ?

 

[Bernie Deitrick]

Get a book - any in the John Walkenbach Power Programming with Excel VBA series will work...

HTH,
Bernie
MS Excel MVP


Bernie,

Thank u very much, it works great.

I need small help, Please help me.

I need to learn advance coding in Excel VBA. Can u guide me ?