Business Solutions:  Excel - Let It Write The VBA Code For You

        by James D. Duncan, CPA   July 2005

      Need a macro to do two-column input for you? Let Excel write it for you. Under tools> macro> record new macro is the method to let Excel build the code for you. You can then modify it for your purpose. Here is the screen you get by selecting tools>macro> record new macro.

 

       Click on the OK button. The normal Excel grid appears. Enter in 10 and press the right arrow, enter 11, press the down arrow, enter 12 and so on. You are entering a two column set of numbers by moving right one cell, down one cell, left one cell and repeating the process.
      Now open the macro up and see what code it has recorded.

 

     Now that we have seen how VBA does the code, we write our own input and we have a
2-column input sheet.

 

 

 

Then click on the stop recording icon. This will save the macro with the name macro1.
      Now let’s look at the code we generated. Click on Tools>Macro>Visual Basic Editor. We can now see the code that was generated.

 

These are the steps that Excel recorded as we made the macro.

Modify the code to read:

Option Explicit

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/24/2005 by James D. Duncan
Dim Numcol As Integer
Dim r As Integer, r1 As Integer
Dim c As Integer, c1 As Integer

Numcol = 2
r = ActiveCell.Row
c = ActiveCell.Column
r1 = r
c1 = c
For r = r1 To r1 + 100 'Increment rows
For c = c1 To c1 + (Numcol - 1) 'Increment columns
Cells(r, c) = InputBox("Column" + Str(c), Str(Numcol) + "Column Input", , , 1) 'input to cell
If Cells(r, c) = "" Then 'check for return only and then exit
Selection.End(xlDown).Select
Exit Sub
End If
Next c
Next r
Selection.End(xlDown).Select

End Sub

Now run the macro and you have a two column input macro.

 

Enter a few numbers and then press return with no entry. You will see that the numbers you have input are in two columns.

 

     This is nice for doing time cards or any multicolumn input. And it is much faster than running each column separately.

     Remember; volunteer your time to help. The agencies need the help and I always get more out of it than they do.

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