| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro code to get dynamic range | Excel Discussion (Misc queries) | |||
| Can I use code/macro to change code/macro in an existing file? | Excel Programming | |||
| macro code to sort a range | Excel Discussion (Misc queries) | |||
| VB Code Naming a Range (range changes each time) | Excel Programming | |||
| Range selection code in a macro | Excel Programming | |||
Linear Mode
