Excel VBA Instance

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.