5

I am trying to make an Excel Macro that asks for a day and then inserts it into the next open cell. here is the code I am trying to use.

Sub Button7_Click()
    Dim AddName As String
    AddName = InputBox("Date: Month/Year", "Add Date", "01/00")
    Sheets("Sheet2").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Range("A1").Value = AddName
End Sub

It is putting the day I type in into cell A1 no matter what is in it, and then selecting the next open cell in row A.

I feel like the answer is so simple but I can't figure it out!

duncan.w
  • 51
  • 2

2 Answers2

4

See How to avoid using Select in Excel VBA macros.

Sub Button7_Click()
    Dim AddName As String
    AddName = InputBox("Date: Month/Year", "Add Date", "01/00")
    With Worksheets("Sheet2")
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = AddName
    End With
End Sub

Your original problem was that after selecting a cell offset down one row from the last used cell in column A, you didn't use that selection to write the value; just wrote it into A1 instead. The last line of your code could be changed to Selection = AddName but it is better to avoid using select and activate whenever possible.

You may want to look into Application.InputBox. The Excel Application.InputBox Method is slightly different than a standard VBA InputBox function in that it allows to specify a type of return.

Community
  • 1
  • 1
4

Here's another:

Sub Button7_Click()
    Dim AddName As String
    AddName = InputBox("Date: Month/Year", "Add Date", "01/00")
    Sheets("Sheet2").Select
    Range("A" & ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row + 1) = AddName
End Sub
Mike Powell
  • 260
  • 1
  • 6
  • 1
    Another bonus to using ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row is it forces excel to clean up any old memory locations for cells that have been deleted – Mike Powell Mar 21 '17 at 18:13