Các câu đố, bài tập nhằm ôn tập & bổ sung kiến thức căn bản VBA

[Hoàng Trọng Nghĩa]

Với tinh thần chơi mà học, học mà chơi, nên tôi đã mở ra topic này, hy vọng các thành viên tham gia, nhất là các thành viên mới biết về VBA.

Sau đây là câu hỏi đầu tiên:

Câu hỏi 1: Bằng phương pháp nào nhanh nhất để tìm ra ô nào trong một cột chứa một điều kiện.

Tôi có 1 file Excel 2007, với cột A, từ A1 đến A1048576 đều có giá trị.

Bằng phương pháp nào nhanh nhất (dùng mảng, dùng For Each v.v...) để tìm ra ô nào trong cột A chứa chữ "Nghia", đồng thời với ô ở cột B tương ứng nhập giá trị "OK" vào đó?

Ví dụ tìm thấy trong ô A2 có giá trị là "Nghia" thì ô B2 nhập vào "OK".

Hiện tại, đáp án nhanh nhất mà tôi có được đã gửi mail riêng (nhằm ghi lại thời gian gửi, để tránh nói ăn gian).

Để tiện việc theo dõi các câu đố, các bài tập tôi đã tạo ra topic này các bạn click vào đây:

Các link của topic "Các câu đố, bài tập nhằm ôn tập & bổ sung kiến thức căn bản VBA"

 

[mhung12005]

Mọi người gửi lên thì em cũng gửi luôn, nhờ các Thầy chỉ bảo, có sửa lại một chút so với bài gửi trong mail Thầy Mỹ.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
    Dim Rng As Range
    Dim I As Long, J As Long
    Set Rng = Sheet1.Range("B4:B" & Sheet1.Range("B65535").End(xlUp).Row)
        If Target.Address <> "$C$2" Then
        Exit Sub
        Else
            Range("A5:E65535").Clear
            For I = 1 To Rng.Rows.Count
                If Rng(I) = Target Then
                    J = J + 1
                    Cells(J + 4, 1) = J
                    Cells(J + 4, 2) = Rng(I).Offset(, 2)
                    Cells(J + 4, 3) = Rng(I).Offset(, 3)
                    [COLOR=#ff0000]Cells(J + 4, 4) = Format(Rng(I).Offset(, 4), "#,##0")[/COLOR]
                    Cells(J + 4, 5).FormulaR1C1 = "=RC[-2]*RC[-1]"
                End If
            Next I
            Cells(J + 5, 2) = "Total"
            Cells(J + 5, 5).FormulaR1C1 = "=SUM(R5C:R[-1]C)"
            [COLOR=#ff0000]Range("E5:E" & J + 5).NumberFormat = "#,##0"[/COLOR]
            Range("A5:E" & J + 5).Borders.LineStyle = 1
            Range("A5:E" & J + 4).Borders(xlInsideHorizontal).Weight = xlHairline
        End If
Application.ScreenUpdating = True
End Sub

Chào bạn leonguyenz,

Trong đoạn code của bạn mình tô đỏ ý, theo mình thì:

Dòng này :

Cells(J + 4, 4) = Format(Rng(I).Offset(, 4), "#,##0")

đổi thành:

Cells(J + 4, 4) = Rng(I).Offset(, 4)

và dòng này:

Range("E5:E" & J + 5).NumberFormat = "#,##0"

đổi thành:

Range("D5:E" & J + 5).NumberFormat = "#,##0"

sẽ nhanh hơn chứ nhỉ vì:

Thay vì format từng Cell một thì mình fomat cả vùng luôn 1 thể cho 2 cột.

 

[ThuNghi]

Theo tôi thây, đây là topic căn bản VBA mà, các bạn biết rồi nên tranh luận về kẻ khung, thậm chí còn nét này nét nọ..., và format.
Cứ học VBA từ đầu làm sao ra đúng đáp số đã, cùng lắm trang trí lại sau.
Phần trang trí nếu nói sâu cũng phức tạp lắm. Tôi thì kg rành pần này nên thường format nhiều dòng và hide lại cho khỏe. Đỡ mất công tư duy mà đáp số lại sai.
Hy vọng các bạn kg phiền, vì lúc tôi học VBA nếu ra đáp số là nhảy tưng rồi.
Cám ơn các bạn.

 

[ptm0412]

Chưa thấy ai dùng Resize nhỉ?

 

[VetMini]

...
Về code đánh số thứ tự thực sự thấy đưa vào vòng lặp như bài này cũng được vì dữ liệu ít nhưng mà mình thấy chiêu này của bác PTM0412 còn hay hơn
Range("A5:A" & lastR - 1) = Evaluate("=Row(R:R)")

Hay thì hay rồi, nhưng không theo sát yêu cầu đề bài

Bài tập mở rộng của For Next bài vừa rồi, Dùng biến j tăng 1 theo điều kiện để ghi dữ liệu thỏa điều kiện sang sheet khác.
...

Theo điều kiện, J là chỉ số thứ tự của dòng chi tiết chứ không phải là thứ tự của dòng ghi - tuy kết quả giống nhau nhưng không cùng ý nghĩa.

Nếu công việc triển khai ra khiến một dòng chi tiết có thể ghi ra nhiều hàng thì người ta chỉ việc đặt thêm 1 biến K để phân biệt dòng ghi và dòng chi tiết.

1    Chi tiết 1
2    Chi tiết 2
     Chi tiết phụ của 2
3    Chi tiết 3
4 ...

 

[hungpecc1]

Em cũng tham gia chút, Thầy Mỹ tiện thể chấm điểm luôn dùm em.

Sub TrichLoc()

  With .Range("A5:E" & dong + 1)
    With .Borders(xlEdgeTop)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      With .Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      With .Borders(xlEdgeRight)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      With .Borders(xlInsideVertical)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      With .Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
    End With
End With
Set myRng = Nothing
End Sub

Đoạn code border sao anh không viết như vậy cho gọn:

For Each Item In Array(7, 8, 9, 10, 11, 12)
      set rng = Range("A5:E" & dong + 1) 
       rng.Borders(Item).LineStyle = 1
Next

 

[ThuNghi]

Chưa thấy ai dùng Resize nhỉ?

Dùng resize hình như dài hơn cells(i,1...) mà khó nữa. Bác cứ làm khó học trò.

 

[hoi_joker84]

Làm theo gợi ý của bác VetMini (dữ liệu là khối liền mạch). Code chạy tại Sheet2
[GPECODE=vb]Sub Bai1()

Dim i As Long, j As Long
Dim SourceRange As Range, DesRange As Range

Set SourceRange = Sheet1.Range("B4:F" & Sheet1.Range("F1000").End(xlUp).Row)
Set DesRange = Sheet2.Range("A5")

Range("A5:E65000").Clear

For i = 1 To SourceRange.Rows.Count
With DesRange
If SourceRange.Cells(i, 1) = [C2] Then
j = j + 1
.Cells(j, 1) = j
.Cells(j, 2) = SourceRange.Cells(i, 3)
.Cells(j, 3) = SourceRange.Cells(i, 4)
.Cells(j, 4) = SourceRange.Cells(i, 5)
.Cells(j, 5).FormulaR1C1 = "=RC[-2]*RC[-1]"
End If
End With
Next i
Cells(j + 5, 2) = "Total"
Cells(j + 5, 5).FormulaR1C1 = "=SUM(R5C:R[-1]C)"

Range("D5:E" & j + 5).NumberFormat = "#,##0"
Range("A5:E" & j + 5).Borders.LineStyle = 1
Range("A5:E" & j + 4).Borders(xlInsideHorizontal).Weight = xlHairline

Set SourceRange = Nothing
Set DesRange = Nothing

End Sub[/GPECODE]

 

[VetMini]

Re: Ressize
Phạm vi của thớt này là gì?
"So sánh tất cả các kỹ thuật viết code từ cơ bản đến nâng cao" hay "Giới hạn trong các kỹ thuật đơn giản để giải quyết vấn đề"

 

[ndu96081631]

Đoạn code border sao anh không viết như vậy cho gọn:

For Each Item In Array(7, 8, 9, 10, 11, 12)
      set rng = Range("A5:E" & dong + 1) 
       rng.Borders(Item).LineStyle = 1
Next

Vậy vẫn dài. Dùng cái này: Range("Gì gì đó").Borders.LineStyle = 1
--------------------

Re: Ressize
Phạm vi của thớt này là gì?
"So sánh tất cả các kỹ thuật viết code từ cơ bản đến nâng cao" hay "Giới hạn trong các kỹ thuật đơn giản để giải quyết vấn đề"

Resize và Offset cũng rất đơn giản mà

 

[Quang_Hải]

Vậy vẫn dài. Dùng cái này: Range("Gì gì đó").Borders.Value= 1
--------------------


Resize và Offset cũng rất đơn giản mà

Em chơi cái đỏ đỏ luôn cho dễ nhớ

 

[ptm0412]

Re: Ressize
Phạm vi của thớt này là gì?
"So sánh tất cả các kỹ thuật viết code từ cơ bản đến nâng cao" hay "Giới hạn trong các kỹ thuật đơn giản để giải quyết vấn đề"

Dùng Resize đúng chỗ ấy chứ Vetmini.

Thay

      .Cells(j, 2) = SourceRange.Cells(i, 3)

      .Cells(j, 3) = SourceRange.Cells(i, 4)
      .Cells(j, 4) = SourceRange.Cells(i, 5)

bằng

      .Cells(j, 2).Resize(1,3) = SourceRange.Cells(i, 3).Resize(1, 3)

3 dòng lệnh gom thành 1 không phải là quá hay, nhưng nếu 5, 7 dòng thì gom thành 1 thì cũng khá hữu ích.

Còn những chỗ khác thì tôi ưu tiên làm theo cách:

Range(Cells(i, j), Cells(m, n))
hoặc
Range("A3:A" & EndRow)

Tùy theo tình huống cái gì có sẵn.

 

[hoi_joker84]

Vậy em làm bài theo hướng này có được ko vậy các anh chị ? (Do bài này dữ liệu liền mạch, ko liền mạch thì Sort cái là ra rồi Resize cả cụm khỏi phải lặp, nhưng lại phải thêm 1 biến k không thì chưa tìm ra cách nào xác định dòng đầu tiên của SourceRange có giá trị = [C2])
[GPECODE=vb]Sub Bai1_Resize()

Dim i As Long, j As Long, k As Long
Dim SourceRange As Range, DesRange As Range

Set SourceRange = Sheet1.Range("B4:F" & Sheet1.Range("F1000").End(xlUp).Row)
Set DesRange = Sheet2.Range("A5")

Range("A5:E65535").Clear

For i = 1 To SourceRange.Rows.Count
If SourceRange.Cells(i, 1) = [C2] Then
k = i
j = j + 1
DesRange.Cells(j, 1) = j
DesRange.Cells(j, 5).FormulaR1C1 = "=RC[-2]*RC[-1]"
End If
Next i
DesRange.Cells(1, 2).Resize(j, 3).Value = SourceRange.Cells(k, 1).Offset(-j + 1, 2).Resize(j, 3).Value

Cells(j + 5, 2) = "Total"
Cells(j + 5, 5).FormulaR1C1 = "=SUM(R5C:R[-1]C)"

Range("D5:E" & j + 5).NumberFormat = "#,##0"
Range("A5:E" & j + 5).Borders.LineStyle = 1
Range("A5:E" & j + 4).Borders(xlInsideHorizontal).Weight = xlHairline

Set SourceRange = Nothing
Set DesRange = Nothing

End Sub

[/GPECODE]

 

[VetMini]

Tôi chỉ nói là phải đến một trình độ nào đó người ta mới có thể nhìn được hình dạng và vị trí của một range. Khi ấy dùng Offset và Resize mới có ý nghĩa.

Trong bài này, thực ra có 3 chỗ để sử dụng Resize:
1. Vì trong quá trình sao chép có giai đoạn cần copy 3 ô liên tiếp từ bên src qua 3 ô liên tiếp bên des cho nên có thể dùng Offset (hoặc Cells) để đến ô đầu tiên của nhóm và áp dụng Resize để phép sao chép hàng loạt.
2. Sau vòng lặp, trị của J chính là số dòng của nhóm dữ liệu vừa chép xong. Vì vậy ta có thể dùng Resize để chép công thức "=RC[-2]*RC[-1]" vào cột thứ 5 của nhóm dữ liệu, thay vì chép từng dòng trong vòng lặp.
3. Tương tự, Resize cũng có thể được dùng để chọn nhóm và kẻ khung.

Để làm được như vậy. Các bạn nói là căn bản sao?

 

[ptm0412]

Để làm được như vậy. Các bạn nói là căn bản sao?

Ở mức độ căn bản, người mới học chỉ cần biết resize là thay đổi kích thước 1 range thành 1 range mới, có cùng ô góc trên bên trái. Range mới này có đầy đủ tính chất của 1 range nên có thể sử dụng mọi property và method của range. Tuy property và method thì bao la, nhưng họ biết method nào xài method đó, vậy thôi.
Trong câu tôi thí dụ thì chỉ là resize 1 ô thành 1 dòng 3 cột (3 ô), có gì khó hiểu đâu? Những chỗ khác khó hơn, tôi không làm mẫu và cũng không bắt làm.

ì vậy ta có thể dùng Resize để chép công thức "=RC[-2]*RC[-1]" vào cột thứ 5 của nhóm dữ liệu, thay vì chép từng dòng trong vòng lặp.

Chỗ này tôi cũng sẽ không dùng resize, mà dùng Range("E5:E" & j)

Kẻ khung tôi cũng dùng Range("A5:E" & j) hoặc Range("A5:E" & j + 1)

 

[ptm0412]

Một số nhận xét và gợi ý qua bài tập liệt kê chi tiết của 1 đơn hàng:

1. Code của phanminhphuong:

a. Cells(j, 2).Offset(, 1) = .Cells(i, 4).Offset(, 1)

Tại sao không là Cells(j, 3) = .Cells(i, 5)? Offset trong trường hợp này làm tăng sự phức tạp của code và khiến cho VBA phải tính toán thêm 1 động tác thừa.

b. j có giá trị ban đầu là 5 là dòng bắt đầu, sau khi tăng dần, j chính là dòng cuối. Vậy sao phải mất công tính lastR = Range("B50").End(xlUp).Row làm gì, xài luôn j cho khỏe?

c. Code kẻ khung của bạn rất gọn: 1 dòng kẻ khung toàn bộ cho cả dòng total, 1 dòng kẻ đường ngang giữa = hairline cho vùng không kể dòng tổng.

2. Code của leonguyen:

Bạn cũng dùng offset, ngoài ra bạn dùng hàm format, kết quả sinh ra chuỗi chứ không phải số, mặc dù khi gán xuống Excel vẫn hiểu.
Hơn nữa, nếu định dạng, thì định dạng cả vùng sau khi lặp, chứ không nên định dạng từng ô trong vòng lặp.

3. Code của phihn:

a. Yêu cầu gán công thức cho cột E chứ không phải gán giá trị. Mặc dù gán giá trị thì nhanh hơn, làm cho file nhẹ hơn, nhưng yêu cầu là ôn lại FormulaR1C1

b. Công thức Sum của bạn rối rắm quá:

      Sheet2.Select
      Range("E" & j).Select
      ActiveCell.FormulaR1C1 = "=SUM(R5C5:R" & j - 1 & "C5)"

- Bạn đang đứng ở Sheet2 nhấn nút cho chạy code, thì cần gì Sheet2.Select?
- Gán công thức trực tiếp cũng được, cần gì Range("E" & j).Select?
- Chỉ cần "=SUM(R5C:R[-1]C)

c. Code kẻ khung của bạn record macro làm sao để nguyên như vậy, nên dài thậm thượt.

d. Bạn không định dạng số có phân cách hàng ngàn.

4. Code của TungNguyen:

Chỉ mỗi tội code kẻ khung "dư cả thúng"

5. Code của hoi_joker84:
Làm theo gợi ý của VetMini, dùng DesRange.Cells(m, n). Cách này là tham chiếu tương đối, nên hơi trừu tượng hơn là địa chỉ dòng cột của Excel.

5. Gơi ý chung:

a. Dùng Resize để gán giá trị 1 lần 3 ô 1 dòng cho mỗi vòng lặp:Cells(j, 2).Resize(1,3) = SourceRange.Cells(i, 3).Resize(1, 3)

b. Dùng trực tiếp Range trên sheet Data, khỏi Set SourceRange, vì cũng chẳng nhanh hơn bao nhiêu. Lại còn phải set Nothing



c. Định dạng số nên đưa ra ngoài vòng lặp, và định dạng số 1 lần cho cả khối

d. Công thức cột thành tiền cũng có thể gán 1 lần cho cả cột, nên hãy đưa ra ngoài vòng lặp:
Range("E5:E" & j).FormulaR1C1 = "=RC[-2]*RC[-1]"

e. Code kẻ khung nên làm theo code của phanminhphuong. Tuy nhiên dự phòng trường hợp đơn hàng chỉ có 1 mặt hàng, nên chỉ có 1 dòng, không có đường kẻ ngang bên trong và sinh ra lỗi.

f. Xóa dữ liệu cũ:

- Nên Clear, không nên Delete dòng
- Clear 1 số dòng vừa phải, không Clear cả 65 ngàn dòng. Ta nên suy nghĩ 1 chút, trước hết nó không thể nhiều hơn EndRow sheet Data được. Kế đến ta có thể ước tính số dòng tối đa cho 1 đơn hàng, vậy Clear 50 dòng hay tối đa 100 dòng là được.

 

[Lê Duy Thương]

Đoạn mảu đỏ ấy thừa cả thúng luôn

DĐoạn màu đỏ là tùng record macro. Không phải tự viết.

 

[VetMini]

Không sao cả. Chấp nhận Resize không khó hiểu. Và cũng chấp nhận Offset không khó hiểu luôn.

Phép toán thường dùng cho sao chép dữ liệu khối (nhiều ô liên tiếp cùng hàng) sang khối khác nó hơi đặc biệt một chút.
Ngưởi ta đặt cái source range là 3 ô đầu, destination range cũng vậy (thường là các ô headings). Và như vậy thì code chép là: datDes.Offset(J).Value = datSrc.Offset(I).Value
Phép này rất thông dụng cho loại bài toán chép dữ liệu có số cột liên tiếp giống nhau nhưng có số dòng so le nhau. Điển hình là đề bài này.

Đăt range nhiều quá tuy hơi rườm rà nhưng bù lại người ta có thể đặt tất cả các thông số về nơi đi nơi đến, số ô cần chép, vv... trước khi tiến vào vòng lặp. Đây là kỹ thuật viết code tách rời phần sửa soạn (đặt thông số) với phần làm việc (vòng lặp duyệt dữ liệu và chép dữ liệu). Code bên trong vòng lặp không cần biết dãy data đang chép có bao nhiêu ô.

 

[leonguyenz]

Không biết anh Nghĩa và các Thầy còn quan tâm đến topic này không nhỉ?
Có thể ít người biết đến, nhưng nếu để topic chìm, rồi cũng chắc chẳng ai biết luôn. Rất mong anh Nghĩa và các Thầy cho tiếp câu đố, bài tập, bổ sung kiến thức căn bản VBA với ạ.

 

[ndu96081631]

Không biết anh Nghĩa và các Thầy còn quan tâm đến topic này không nhỉ?
Có thể ít người biết đến, nhưng nếu để topic chìm, rồi cũng chắc chẳng ai biết luôn. Rất mong anh Nghĩa và các Thầy cho tiếp câu đố, bài tập, bổ sung kiến thức căn bản VBA với ạ.

Nhớ lại ngày xưa tôi học hơi khác: Tôi chẳng bao giờ đợi các thầy ra đề mà luôn tự mình nghĩ đề tài và tự giải
Nếu Thảo quan tâm cũng nên học theo cách này ---> Lấy công việc hằng ngày làm để tài chẳng han
Excel nó vô vàn và đề tài cũng không bao giờ thiếu

 

[Quang_Hải]

Không biết anh Nghĩa và các Thầy còn quan tâm đến topic này không nhỉ?
Có thể ít người biết đến, nhưng nếu để topic chìm, rồi cũng chắc chẳng ai biết luôn. Rất mong anh Nghĩa và các Thầy cho tiếp câu đố, bài tập, bổ sung kiến thức căn bản VBA với ạ.

Thảo đã cày hết cái file bài tập anh gởi cho Thảo chưa mà ham hố quá vậy? Hỏng lẻ học nhanh vậy sao ta?
Học hành gì mà mới 10g đã out rồi.