Clever Geek Handbook

Create an Excel macro for fast data entry: VBA: Excel:

I have a job to enter survey results (in paper form) in order to do well. I've never written any macros in Office :(

Here I am what I basically need:

  • I have predefined columns (| A | B | ... | AG | AH |)
  • All polls are grouped into groups. All polls from the same group have several (for example, predefined) identical columns. These are always the same columns that define the group
  • All other answers to the survey are in numeric type [1..10].
  • Columns are not the same as answers in servey
  • I want a macro to take my data (eg "1575") and first put the predefined values ​​for this "group" in | A | | B | | C | and then | E | = 1, | D | = 5, | F | = 7, | G | = 5 and automatically starts entering the next line.

Anything to give me a hint on how to write this macro in more than welcome

Thank you so much for reading this ...

EDIT1: I suppose the question is not clear enough ... I need a macro that will read my keyboard input ('1575') and write the integers '1' '5' '7' and '5' to predefined strings. At the moment I have an idea to create a form, but I need an event handler that changes focus to the next input when I press a key, as I want not to press TAB all the time ...

+1


source to share


3 answers


It seems to me that you want a small userform with a textbox, a range to display the order of data entry, e.g .:

Group A | E | D | F | G

And a little code to go with the form say:

Dim LastCol As Integer
Dim CurRow As Integer


Private Sub UpdateCells()
Dim Col As Variant
Dim ColumnOrder As Range

'Range that specifies data entry order'
Set ColumnOrder = Range("A3:A15")

LastCol = LastCol + 1

If LastCol > WorksheetFunction.CountA(ColumnOrder) Then
    LastCol = 1
    CurRow = CurRow + 1
End If

Col = Range("A3").Offset(0, LastCol)
Range(Col & CurRow) = TextBox1.Value
TextBox1 = ""

End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
    UpdateCells
End If
End Sub

Private Sub UserForm_Initialize()
CurRow = ActiveCell.Row
End Sub

Edit comment



In Excel, the last row can be a little tricky in that Excel does not update the range after deletion until the workbook is saved. One of them can satisfy:

Function FindLastRow()
'Assuming that data starts in A1'
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
FindLastRow = r
End Function

...

Sub LastRow()
LastRowA = ExecuteExcel4Macro("GET.DOCUMENT(10)")
LastRowB = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
End Sub

More information: http://www.tek-tips.com/faqs.cfm?fid=2115

+3


source 


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sText As String

    Application.EnableEvents = True

    If Target.Column = 1 Then  'Four digits entered in col A
        sText = CStr(Target.Value) 'convert to string
        If Len(sText) = 4 Then
            Target.Offset(0, 4).Value = Left$(sText, 1) 'write to E
            Target.Offset(0, 3).Value = Mid$(sText, 2, 1) 'D
            Target.Offset(0, 5).Value = Mid$(sText, 3, 1) 'F
            Target.Offset(0, 6).Value = Mid$(sText, 4, 1) 'G
        End If
    End If

    Application.EnableEvents = True

End Sub


If you enter four digits in column A, it will use them correctly. It comes in a worksheet, not a standard module.

+1


source


If I understand the question correctly, you want the user to be able to enter numbers only without typing or an arrow to complete the entry.

For example, by typing "1253216 ..." instead of "1 2 5 ..."

If this is the case, unfortunately there is no event that matches the individual characters included in the formula bar. The only events that are related to remoteness are the change and change of choice. However, I don't think there is any way to use them for this purpose (they don't get triggered when you type a formula string).

One idea: enter and enter a text formatted column and ask users to type their answers in one big line (like "1253216", etc.), and then put formulas in each of the other cells that parse the nth character from the input column and changes it to a value. For example:

Apply the range name "Inputs" to column A, range name "N" to row 1, and then in any of the other cells, place this formula:

=VALUE(MID(Inputs,N,1))

The downside is that the user won't know if they made a mistake until they finish entering the entire string of numbers and press the enter key or arrow.

-1


source






More articles:


All Articles