Excel VBA Instance

Have a friend to help look at the Excel data how to deal with, the initial look a bit complex, difficult to start. Further analysis, search, found that VBA can be a good solution to this problem, summary record.

Description of the problem: for example, there are n sheet, each sheet records a company's transaction details, demand: Statistics out of all companies, each kind of product trading situation.

Analysis and arrangement of ideas

Initially, these clutter data processing is cumbersome, first of all cross-table statistics, and then the data law is not so obvious. Self-study analysis is a regular:

1. The position of the company is fixed--C4
2. The starting position of the transaction details is fixed-B9
3. The closing method of the transaction details is fixed--space

The overall solution to the idea of building a new sheet, accelerating to sum:

1. Take company name as first column from C4 location
2. From B9 start down search, B10, B11 ..., take B9 as column 2nd, quantity F9 as 3rd column, make a row of data
3. The search end condition is that bn is a space
4. Use the same treatment for all other sheet

Code Module Learning

After you have determined that the processing scheme is VBA, learn the basic VBA

Select a fixed table

Sub test1 () ' gives cell C1 the value of cell D6 a range ("C1") = Range ("D6"). Value ' for cell B1 gives cell A1 values in the way two cells (3, 2) = Cells (3, 2). ValueEnd Sub

Of these, cells (i,j) I refers to the number of rows, J refers to the number of columns, cells (i,j) refers to the first row of column J cell.

Looping expressions using

Sub test2 () Dim I as Integer ' for loop use for i = 1 to ten ...    Next ' while loop using while I < ...    Wendend Sub

Traverse all Sheet

Sub test3 () Dim myworksheet as Worksheet ' traverse all sheetfor myworksheet in worksheets    ' ... NextEnd Sub

Final code implementation

Sub Calc ()    ' defines an incremental loop i    Dim myworksheet As Worksheet    Dim i As Integer    Dim j As Integer        i = 1                                                         ' summary table from the first row    Starting Debug.Print "Start testing" for    each myworksheet in worksheets                            ' traverse all sheet        Debug.Print Myworksheet.name        j = 9        Debug.Print J        While Myworksheet.cells (J, 2) <> ""                       ' End condition bn is empty            Sheets ("Calc"). Cells (i, 1) = Myworksheet.cells (4, 3)  ' The first column puts the name            Sheets ("Calc"). Cells (i, 2) = Myworksheet.cells (j, 2)  ' The second column puts the model            Sheets ("Calc"). Cells (i, 3) = Myworksheet.cells (j, 6)  ' third column put quantity            i = i + 1                                             ' summary table Move down line            j = j + 1                                             ' b column search down        wend
   next Myworksheetend Sub

Datasheets and other details open the VBA code window

Right-click any sheet and select View Code . or shortcut key alt+f11

FILE--Options--Customize Ribbon, Main tab of common commands you can select the development tool, open the menu bar, you can see the Visual Basic and the Macro menu directly

Debugging methods

Debug.Print, support variables, strings, etc., print content output in Immediate window, Immediate window open method:

View-Immediate window. or shortcut key Ctrl+g

Text to Number

The final generated quantity column is text, cannot do addition statistics, need to first converted to a number

Select all text, yellow exclamation mark in upper left corner, mouse move past, select Convert to Number

Example form Download

Https://files.cnblogs.com/files/quantumplan/vba%E5%AE%9E%E4%BE%8B.rar

Excel VBA Instance