Business Solutions: Excel: Quick, Two Column Entry
      By James D. Duncan, CPA      January 2006

       Hope you all had a Merry and enjoyable Christmas. The holidays are usually a wonderful time to see the relatives and enjoy the food that we only get at this time of year. I got a little behind with all the festivities and all, so this will be a quick and easy Excel macro.
       Since we left town for several days I had a chance to read up on “Excel 2003 Power Programming with VBA” by John Walkenbach while on the road. Reading in the car makes the long trips go faster. I took this code from his book.
       Let’s create a macro that allows input of one column for names and the other column for amounts. We want to enter the name and then enter the amount. You can modify this for whatever description you need.
       Click on Tools, Macro, Visual Basic Editor. Double click on This Workbook to open the code window to enter the code into. Then enter the following code:

Sub GetData()
Dim NextRow As Long
Dim Entry1 As String, Entry2 As String
Do
NextRow = Range("A65536").End(xlUp).Row + 1
Entry1 = InputBox("Enter the name")
If Entry1 = "" Then Exit Sub
Entry2 = InputBox("Enter the amount")
If Entry2 = "" Then Exit Sub
Cells(NextRow, 1) = Entry1
Cells(NextRow, 2) = Entry2
Loop

       Move back to the spreadsheet and run the macro. You will get an input box that lets you enter the name.

Enter the name and press the Enter key and the next box lets you enter the amount.

Enter several names and amounts and then just press enter to exit the input loop.



And you have a quick, 2-column input for names and amounts. The book builds on these examples and adds additional code like validation.
      Looking at another person’s code always gives you ideas and insights into things you can build for your spreadsheets. And I always make a mistake or two keying in the code so get to explore debugging and error correction too! Such is the path of life.

Best wishes for the New Year. Make a list of resolutions, you can always use a laugh.

 

James is a member of the CCOKC and an expert in Access and Excel. Click here to reach James