Tutorial :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.


Solution: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  


Solution:2

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.


Solution: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


Solution:4

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

ActiveCell.Offset(1, -4).Select  


Solution: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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »