Excel VBA code to automate

Question

I have recorded the following VBA macro in Excel

Sub EnterDate()
'
' EnterDate Macro

' Enter date at any point in a worksheet and move cursor down

'

' Keyboard Shortcut: Ctrl+x

'
    ActiveCell.FormulaR1C1 = "12/15/2014"

    ActiveCell.Offset(1, 0).Range("A1").Select

End Sub

What I need to be able to do is to be able to insert different years, months, and days, and loop back to repeat the process in the cells below. The above code only inserts the defined date. I would like to enter month, hit [ENTER], enter day, hit [ENTER], enter year hit [ENTER], display the date and move down to the next cell, repeating the loop until stopping execution.

ActiveCell.Offset(1, 0).Range("A1").Select in nonsensical. Starting from the active cell, you move down one row and then select cell A1? Did you mean Range("A1").Offset(1,0) instead? — John Alexiou
– John Alexiou, Commented Jul 29, 2014 at 20:25
Why can't you just type the dates you want into Excel? Is there a sequence here or anything repeating? — John Alexiou
– John Alexiou, Commented Jul 29, 2014 at 20:27

David Zemens · Accepted Answer · 2014-07-30 20:17:43Z

1

Out of curiosity, why would you use Ctrl+x (built in hotkey for "Cut" command) to hotkey this macro procedure?

Simple Do ... Loop with a messageBox to prompt you to continue (or quit) and InputBox to capture the values you want to put on the sheet.

Sub EnterDate()
'
' EnterDate Macro

' Enter date at any point in a worksheet and move cursor down

'

' Keyboard Shortcut: Ctrl+x

Dim mb as VbMsgBoxResult
Dim y as String, m as String, d as String

Do 

    y = Application.InputBox("Year?")
    m = Application.InputBox("Month?")
    d = Application.InputBox("Day?")

    ActiveCell.Value = m & "/" & d & "/" & y
    ActiveCell.Offset(1, 0).Select

    mb = MsgBox("Continue?", vbYesNo)
Loop While Not mb = vbNo

End Sub

edited Jul 30, 2014 at 20:17

answered Jul 29, 2014 at 18:14

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TexasBob Over a year ago

Many thanks, David. I am just trying to learn VB, weaning myself away from Lotus123 which I love only because of its simplicity to write macros. This is my first simple exercise. In Lotus123, a macro named \x with the code "@date({?},{?},{?})~{down}{branch \x}" does the job perfectly. In Excel, using VB, it ain't so simple. Your code works beautifully, except that selecting "No" for "Continue?" does not stop execution. Not a big deal, but I love your approach. Thanks for the help.

David Zemens Over a year ago

Try now, I forgot to set the mb variable to the message box result :)

TexasBob Over a year ago

Works smoother than a baby's bottom! I'm now really getting interested in learning more VB - I used to program in Basic many, many years ago. I greatly appreciate help from folks like you.

Collectives™ on Stack Overflow

Excel VBA code to automate

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related