Tools for report research-VBA

I have been studying VBA and writing reports recently. I would like to sum up my knowledge and experience and share it with you.

Tools, VBA

1. One of the best functions of Excel is to record macros. When a function is not sure how to write it, recording will be very helpful when it is represented by a function.

2. Compared with the recording macro, VBA can be more precise, smarter, clearer in logic, and more effective in execution. In particular, its cycle and Recursion directly free people's hands.

3. Basic VBA operations

(1) positioning:

Basic Unit: sheets, rows, columns, cells, and range)

Compared with each basic unit, we can select, clear, unmerge, and merge ). delete, activate, copy, and paste)

In fact, it is not limited to a column in a row. A more effective method is rule control. For example, the last row of a page and the last column of a page.

This method is more flexible.

Lastrow = sheets ("name"). Range ("a65536"). End (xlup). row' retrieves the row number of the last row of data in the data table

Lastcol = sheets ("name"). cells (6,255). End (xltoleft). Column 'takes the data in the last column of the sixth row of the data department.

(2) Time

If you want to locate the time, such as today

1. Use the EXCEL function directly. And only take its value, not the function.

Range ("D6"). Select
Activecell. formular1c1 = "= Today ()"
Range ("D6") = range ("D6"). Value

2. VBA function date.

Range ("D6"). Select

Range ("D6"). value = Date

(3). Layout

Column width

Columns ("C"). columnwidth = 5
Columns ("B"). columnwidth = 22
Range (columns (4), columns (40). columnwidth = 9

Row width

(4). Loop

While

........

Wend

Advantage: when there are two tables or two variables, this while is particularly useful.

For I = 0 to 18 Step 2

......

Next

Advantage: It is especially suitable for a table.

 

(5) condition judgment

If... then... a powerful tool for condition judgment

(6) Statistics

1 countif. application. countif (range (cells (8, 3), cells (32, 3), "up") indicates the number of "up" in the statistical range.

2 Vlookup. application. Vlookup (cells (mnum, 2), worksheets ("UMP key value"). Range ("A: I"), 9, 0) query function.

(7) create a new page and name it

Sheets. Add after: = sheets (sheets. Count)

AA = sheets ("name"). cells (38, I)
Sheets (sheets. Count). Name = AA
Sheets (sheets. Count). Select

(8) hyphens &. It is extremely viscous and connects characters and numbers.

AA = sheets ("UMP key value"). Range ("I" & I)
AB = sheets ("UMP key value"). Range ("J" & I)
AC = AA & "-" & AB

 

Tools for report research-VBA