Macro code to set range
 

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

Macro code to set range
 

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" wrote in message
...
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

Macro code to set range
 

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 ?






On Apr 28, 7:13*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Macro code to set range
 

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

HTH,
Bernie
MS Excel MVP


"muddan madhu" wrote in message
...
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 ?






On Apr 28, 7:13 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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" wrote in message

...



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- Hide quoted text -

- Show quoted text -