Make Macro to input data into Excel spreadsheet

Question

I am using Excel 2003 and I need to create a macro the asks the user for 5 inputs: Date, Project#, Fault, Problem, and Solution. I have never used macros before so I have no idea how to code this. Once the data is entered by the user I want it to go under the appropriate columns. After this is done the macro will not prompt the user for another input until the macro is run again.

Answer 1

Sub TestMacro()  
    Range("A2").Select
    ActiveCell.FormulaR1C1 = InputBox("Date")
    Range("B2").Select
    ActiveCell.FormulaR1C1 = InputBox("Project #")
    Range("C2").Select
    ActiveCell.FormulaR1C1 = InputBox("Fault")
    Range("D2").Select
    ActiveCell.FormulaR1C1 = InputBox("Problem")
    Range("E2").Select
    ActiveCell.FormulaR1C1 = InputBox("Solution")
End Sub

Answer 2

To move the active cell down to a3 (i.e. the next row underneath as you ask) use this:

ActiveCell.Offset(1, -4).Select

Answer 3

There is a built-in way of doing this. Put your column headers in row 1 of an Excel sheet, select cell A1 then go to the Data menu and choose Form

This can be a restrictive solution as you can't do things like populating one field on the basis of another (e.g. give a list of problems which depend on the chosen fault). If you just need something quick and simple then it does the job

Answer 4

When you start the macro, place this code before you do anything:

Dim i As Integer

Range("a1").Select
Range(Selection, Selection.End(xlDown)).Select
i = Selection.Rows.Count

This assumes that you always have some value in 'A1' and that there are no blank rows until you come to the end of the valid rows of entered data.

At this point, you have the value 'i' that is equal to the number of rows already containing data.

Then you can use Christian Payne's code above, but modify the

Range("A2").Select

to

Cells(i + 1, 1).Select

This will allow your macro to place data in the next blank row below your already existing data.

Answer 5

Would something like this do what you need?

Sub TestMacro()
Dim dblRow As Double, dtDate As Date, strProject As String
Dim strFalut As String, strProb As String, strSol As String
    dblRow = InputBox("What Row to Enter On")
    dtDate = InputBox("Date", , Date)
    strProject = InputBox("Project #")
    strFalut = InputBox("Fault")
    strProb = InputBox("Problem")
    strSol = InputBox("Solution")
    Range("A" & dblRow).Value = dtDate
    Range("B" & dblRow).Value = strProject
    Range("C" & dblRow).Value = strFalut
    Range("D" & dblRow).Value = strProb
    Range("E" & dblRow).Value = strSol
End Sub