Bài viết: Thuộc tính End() trong Excel - giới thiệu tổng quan (P2)

[SA_DQ]

4. Chuyển vị dữ liệu trong cột thành hàng
​

Nếu ta có nhiều cột dữ liệu và mỗi cột có rất nhiều hàng, đoạn mã sau dùng để chuyển vị chúng sang một sheet khác.

[FONT=Courier New][COLOR=navy][COLOR=navy]Sub TransposeThem()[/COLOR]
[COLOR=navy]Dim iRows As Long:                  Dim iCol As Integer[/COLOR]
[COLOR=navy]Application.ScreenUpdating = False[/COLOR]
[COLOR=navy]Application.DisplayAlerts = False[/COLOR]
[COLOR=navy]On Error Resume Next[/COLOR]
[COLOR=navy]Sheets.Add().Name = "Trans"[/COLOR]
[COLOR=navy]On Error GoTo 0[/COLOR]
[COLOR=navy]If ActiveSheet.Name <> "Trans" Then[/COLOR]
[COLOR=navy]ActiveSheet.Delete[/COLOR]
[COLOR=navy]Sheets("Trans").Cells.Clear[/COLOR]
[COLOR=navy]End If[/COLOR]
 
[COLOR=navy]Application.DisplayAlerts = True[/COLOR]
[COLOR=navy]For iCol = 1 To Range("IV1").End(xlToLeft).Column[/COLOR]
[COLOR=navy]For iRows = 1 To Columns(iCol).Range("A65536").End(xlUp).Row Step 256[/COLOR]
[COLOR=navy]  Columns(iCol).Range("A" & iRows _[/COLOR]
[COLOR=navy]     & ":" & "A" & iRows + 255).Copy[/COLOR]
[COLOR=navy]  Sheets("Trans").Range("A65536").End(xlUp).Offset _[/COLOR]
[COLOR=navy]    (1, 0).PasteSpecial Transpose:=True[/COLOR]
[COLOR=navy]Next iRows[/COLOR]
[COLOR=navy]Next iCol[/COLOR]
[COLOR=navy]Application.ScreenUpdating = True[/COLOR]
[COLOR=navy]End Sub[/COLOR]
[/COLOR][/FONT]

Ta nên chọn toàn bộ cột trước khi chạy đoạn mã này.

5. Lập danh sách duy nhất từ 1 danh sách có trùng & sắp xếp theo trật tự​

Chúng ta đang có 1 danh sách liệt kê các mã hàng hóa đã bán trong ngày đang ở cột ‘A’ của sheet3. nhu cầu đề ra là lập danh sách duy nhất từ chúng & sắp xếp theo thứ tự cho dễ quan sát & thống kê. Macro sau đây sẽ trợ giúp chuyện đó.

[FONT=Courier New][COLOR=black][COLOR=black]Option Explicit[/COLOR]
[COLOR=black]Sub XepVaXoaTrung()[/COLOR]
[COLOR=black]Dim rSortList As Range, rOldList As Range[/COLOR]
[COLOR=black]Sheet2.Select[/COLOR]
[COLOR=black]Range("A1", Range("A65536").End(xlUp)).Clear[/COLOR]
[COLOR=black]Set rOldList = Sheet3.Range("A1", Sheet3.Range("A65536").End(xlUp))[/COLOR]
[COLOR=black]rOldList.AdvancedFilter Action:=xlFilterCopy, _[/COLOR]
[COLOR=black]CopyToRange:=Cells(1, 1), Unique:=True[/COLOR]
[COLOR=black]Set rSortList = Range("A1", Range("A65536").End(xlUp))[/COLOR]
[COLOR=black]With rSortList[/COLOR]
[COLOR=black].Sort Key1:=.Cells(2, 1), Order1:=xlAscending, Header:=xlYes, _[/COLOR]
[COLOR=black]   OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom[/COLOR]
[COLOR=black]End With[/COLOR]
[COLOR=black][A1] = "Danh Sach Xep Duy Nhat"[/COLOR]
[COLOR=black][A1].Interior.ColorIndex = 35[/COLOR]
[COLOR=black]End Sub[/COLOR]
[/COLOR][/FONT]

Macro sau khi thực thi nhiệm vụ, sẽ lập ra tại Sheet2 danh sách duy nhất mà ta đang cần.

6. Copy một vùng dữ liệu đến một cột theo số lần cho trước​

Macro sau đây sẽ tạo vòng lặp để chép dữ liệu từ 1 vùng cho trước, đến các ô trên 1 cột. Số lần chép tùy thuộc vô số dòng có trong một vùng khác.
Nhưng cái mà chúng ta cần chú ý thêm, tác giả dùng tính tương đối của vùng ô, cũng có tác dụng như dùng thuộc tính Offset(I, j)

[FONT=Courier New][COLOR=black][COLOR=black]Sub CopyPaste()[/COLOR]
[COLOR=black] Dim rCopy As Range:                 Dim lRow As Long[/COLOR]
[COLOR=black] lRow = Cells(Rows.Count, 3).End(xlUp).Row + 4[/COLOR]
[COLOR=black] Set rCopy = Range(" A2:B8")[/COLOR]
 
[COLOR=black] Do Until Cells(Rows.Count, 4).End(xlUp)(2, 1).Row >= lRow[/COLOR]
[COLOR=black]     rCopy.Copy Cells(Rows.Count, 4).End(xlUp)(2, 1)[/COLOR]
[COLOR=black]     MsgBox Cells(Rows.Count, 4).End(xlUp)(2, 1).Address, , _[/COLOR]
[COLOR=black]         Cells(Rows.Count, 4).End(xlUp).Address[/COLOR]
[COLOR=black]  Loop[/COLOR]
[COLOR=black] Cells(Rows.Count, 3).End(xlUp).Range("B2:C5").Clear[/COLOR]
[COLOR=black]End Sub[/COLOR]
[/COLOR][/FONT]

7. Thuộc tính End hỗ trợ tính định thức ma trận​

Macro dưới đây dùng để xác định nghiệm hệ phương trình tuyến tính 3 ẩn số bằng cách xác định định thức các ma trận của chúng. Ma trận các hệ số của chúng đang được thể hiện tại vùng ‘B1:E3’ (Xin xem hình)
hai dòng lệnh đầu tiên dùng để khai báo các biến cần dùng.
Dòng lệnh 1: Mảng 2 chiều, mỗi chiều 3 phần tử được đem gán vô biến kiểu Range;
Câu đầu của dòng lệnh 3: Xác định định thức của ma trận;
Câu sau: Dọn dẹp bãi trống để làm việc
Dòng 4: áp dụng thuộc tính End sang trái từ ô đầu tiên của cột cuối cùng trang tính; Nếu là trang tính bình thường, ta sẽ được đưa về ‘E1’ . Sau đó bằng thuộc tính Resize(3) ta đã gán các ô ‘E1:E3’ vô biến dRng;
Dòng lệnh từ 5 đến 10: tạo vòng lặp để xử lý các công việc sau:
· Dòng 6: Vùng G1:I3 được gán trị từ biến Rng. (Biến này chứa giá trị các hệ số của ba ẩn của hệ phương trình tuyến tính).
· Dòng 7: các trị số chứa trong biến dRng (thực ra đó là các hằng số của các phương trình tuyến tính (Xem lại dòng lệnh 6) lần lượt được thay vô các cột ‘G’, ‘H’ & ‘I’ theo 3 lần lặp của vòng lặp;
· Dòng 8: các giá trị tại vùng [G1] mở rọng sang phải & xuống dưới 3 giá trị được đem gán vô biến tạm rTemp; Có vậy mới có thể tiến hành tính định thức cho mỗi lần lặp của vòng lặp
· Dòng 9: 3 ô tại cột thứ 6, theo giá trị tăng dần của vòng lặp sẽ được gán lần lượt nghiệm của các ẩn số của phương trình tuyến tính.

[FONT=Courier New]Option Explicit
Sub Matrix3()
Dim bj As Byte, Fnc As Object:     Dim Dd As Double
Dim Rng As Range, dRng As Range, rTemp As Range
1 Set Rng = [b1].Resize(3, 3)
Set Fnc = Application.WorksheetFunction
3 Dd = Fnc.MDeterm(Rng):       [f1].Resize(9, 9).Clear
Set dRng = [iV1].End(xlToLeft).Resize(3)
5 For bj = 7 To 9
[g1].Resize(3, 3) = Rng.Value
7   Cells(1, bj).Resize(3) = dRng.Value
Set rTemp = [g1].Resize(3, 3)
9   Cells(bj - 2, 6) = Fnc.MDeterm(rTemp) / Dd
Next bj
End Sub
[/FONT]

8. Thêm công thức vô một vùng các ô​

a*/ Dùng bộ thu macro của excel để ghi lại các lệnh thêm công thức cho 1 cột.
Chúng ta có trang tính với cột ‘E’ chứa số liệu từ ‘E3:E25]. Các ô kề bên phải của nó cần bổ sung công thức = (Cột E) / 60

Chúng ta tiến hành ghi macro nhờ bộ thu sẵn có của excel.
Trước tiên ta mở bộ thu macro. Chọn ô [F3] & nhập vô đó công thức =E3/60.
Bấm chọn sau đó ô [33]. Giữ phím {CTRL} & dùng phím mũi tên xuống cho đến cuối cột có chứa dữ liệu.
Bấm phím mũi tên phải, nhấn tổ hợp các phím {SHIFT}+{CTRL} & mũi tên lên cho đến khi [F3] được kích hoạt.
Sử dụng {CTRL}+D để gán toàn bộ vùng cho có cùng công thức như tại [F3].
Hình phía dưới đang thể hiện ảnh màn hình tại thời điểm này khi thu macro bằng bộ thu của excel.

Sub Test0()
 Range("F3").Select
 ActiveCell.FormulaR1C1 = "=RC[-1]/60"
 Range("E3").Select:                                      Selection.End(xlDown).Select
 Range("F25").Select:                          Range(Selection, Selection.End(xlUp)).Select
 Selection.FillDown
End Sub

Chúng ta thấy, phương thức select được lặp lại nhiều lần trong macro.
Nghi vấn được đặt ra: Liệu có cách nào rút gọn macro lại được chăng?
Nhất là phương thức Select thường làm chậm đáng kể tốc độ xử lý.

Hình 2 Ảnh màn hình sau khi bấm {CTRL}+D ​

b*/ Trước tiên ta thử dùng thuộc tính Offset() để thay cho một số chổ có mệnh đề Select trong macro Test0 trên.
Để thuận tiên, ta khai báo thêm biến Rng có kiểu dữ liệu Range.

Sub Test1()
Dim Rng As Range
Set Rng = Range("E3")
Rng.Offset(0, 1).FormulaR1C1 = "=RC[-1]/60"
Set Rng = Range(Rng, Rng.End(xlDown))
Set Rng = Rng.Offset(0, 1)
Rng.FillDown
End Sub

Ta lập bảng so sánh các câu lệnh giữa 2 macro:, như sau

Sub Test0()                                               Sub Test1()
                                                          Dim Rng As Range             
Range("F3").Select                                        Set Rng = Range("E3")
ActiveCell.FormulaR1C1 = "=RC[-1]/60"                     Rng.Offset(0, 1).FormulaR1C1 = "=RC[-1]/60"                                            
' .   .     .   .     .   .     .   .     .   .     .   .     .   .     .   .     .   .     .'
Range("E3").Select                                        Set Rng = Range(Rng, Rng.End(xlDown))         
Selection.End(xlDown).Select
'' .   .     .   .     .   .     .   .     .   .     .   .     .   .     .   .     .   .     .'
Range("F25").Select                                       Set Rng = Rng.Offset(0, 1)
Range(Selection, Selection.End(xlUp)).Select              Rng.FillDown 
Selection.FillDown
End Sub                                                   End Sub

(Chúng ta đã hay sẽ gặp trong Ebook này thuộc tính Offset())
c*/ Tại phần trên chúng ta dùng thuộc tính Offset đã cải thiện đáng kể tình hình.
Nhưng còn có thể cải thiện hơn được không? Ví dụ 2 câu lệnh cuối cùng trong macro trên:

Set Rng = Range(Rng, Rng.End(xlDown)).Offset(0, 1)
Rng.FillDown

Bỡi lẽ Rng là biến thuộc loại Range, nên ta có thể gộp chung 2 dòng lệnh này thành

Range(Rng, Rng.End(xlDown)).Offset(0, 1).FillDown

Điều này có nghĩa là ta không thật sự cần tất cả tham chiếu đến biến Rng.
Đây là một cách thuận tiện đáng nhớ để tham chiếu đến ô E3.
Ta có thể dùng lệnh With như sau:

Sub Test2()
With Range("E3")
.Offset(0, 1).FormulaR1C1 = "=RC[-1]/60"
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 1).FillDown
End With
End Sub

Ta lại lập bảng so sánh như 2 macro trên:

Sub Test1()                                     Sub Test2()
Dim Rng As Range
Set Rng = Range("E3")                             With Range("E3")
Rng.Offset(0, 1).FormulaR1C1 = "=RC[-1]/60"          .Offset(0, 1).FormulaR1C1 = "=RC[-1]/60"
' .   .     .   .     .   .     .   .     .   .     .   .     .   .     .   .     .   .     .'
Set Rng = Range(Rng, Rng.End(xlDown))
Set Rng = Rng.Offset(0, 1)                           Range(.Cells(1, 1), .End(xlDown)).Offset(0, 1).FillDown
Rng.FillDown                                       End With
End Sub                                          End Sub

Nếu giờ ta đối chiếu giữa 2 macro Test0() & Test2() ta thấy chúng chỉ có họ hàng bà con nhau gì đó rất xa nhau!
Đó là tại [E3] ta cùng lập công thức giống nhau.
Như vậy cũng là mong muốn giới thiệu với các bạn thêm một phương cách sửa nội dung macro
ghi được từ excel thành 1 macro có hình thức & nội dung ‘Hoành tráng” hơn.

Một số bài viết có liên quan:
1/ Thuộc tính End() trong Excel - giới thiệu tổng quan (P1)
2/ Tham số hình thức và tham số thực sự
3/ Một số hàm và thủ tục làm việc với Name trong VBA
4/ Hướng dẫn truyền tham số trong VBA (ByVal & ByRef)
5/ Tổng quan về Scripting.Dictionary
6/ Hiểu biết thêm về phương thức Evaluate - Understanding Evaluate Method
7/ Tạm dừng VBA bằng cách sử dụng Wait, Sleep hoặc Vòng lặp
8/ Viết code để nhìn thấy ai là người cập nhật bảng tính của bạn lần gần đây nhất
9/ 4 cách sử dụng Immediate Window trong VBA hiệu quả hơn
10/ 3 gợi ý nhỏ mang lại thành công trong khai báo biến trong VBA
http://www.giaiphapexcel.com/vbb/content.php?553