Những bài tập VBA đơn giản dành cho những người mới bắt đầu (2 người xem)

[SA_DQ]

Bài 01

Macro to merge values from one column into one cell and retain source formatting.
​

Example:

Source:
A1= "It is going to cost "
A2= "$1000.00" (A2 is formatted to underline value)

Destination: (desired result)
B2= "It is going to cost $1000.00" (A2 value is still underlined)

Đề bài có thể tóm gọn lại như sau:

Trên cột [A:A] ta có những dòng thuyết minh & dưới nó là những con số đã được định dạng bằng nhiều cách khác nhau để fân biệt như chữ in nghiên, chữ số được tô đậm hay Font có màu đỏ,. . . .

Macro có nhiệm vụ: Hễ dòng nào có số thì ô bên fải liền kề cần được mang nội dung cũa ô trên ô có số & bản thân số của ô đang xét; Mặt khác định dạng ô giống với ô mang số liệu

Chúc thành công

​

Bảng liệt kê:

TT | Tên bài | Tại | Diễn giải
01|Bài tập 01|#1|Nối chuỗi & định dạng
02|Bài tập 02 | #11|Thống kê số lần lặp
03|Bài tập 03|#19|Trích lọc danh sách theo năm
04|Bài tập 04|#27|Thêm dòng theo số liệu tháng - năm
05|Bài tập 05|#31|Tổng hợp số liệu hoạt động theo từng kỳ (tháng)
06|Bài tập 06|#73|Ghí chú ngày có chi fí lớn nhất trong từng tháng khảo sát
07|Bài tập 07|#84|Thêm dòng tính tổng, sau khi đã thống kê số liệu
08|Bài tập 08|#103|Kẻ dòng, viền khung & format báo cáo hoàn chỉnh
09| BT Fần B | #206 | (Ở đây có bảng liệt kê riêng)

Rất mong các bạn ủng hộ & hỗ trợ tối đa.
!

 

[Ba Tê]

Ah hiểu rồi, mọi người đang tính bắt đầu từ ngày, mình lại cứ theo tháng mà phang, trật lất hết rồi.

Nếu...Nếu...Nếu mà xài WF thì cứ lập công thức, xong "gồi" Record Macro như vầy xem (chưa có cột ghi chú):

Sub GPE()
Dim I As Long, SoThang As Long
   With ActiveSheet
      .[B8:E1400].ClearContents
      SoThang = DateDiff("m", .[D4], .[D5]) + 1
      For I = 1 To SoThang
         .Cells(I + 7, 2).Value = I
         .Cells(I + 7, 3) = Format(DateAdd("m", I - 1, .[D4]), "mm/yyyy")
      Next I
        .Range(.[C8], [C65000].End(xlUp)).Offset(, 1) = _
        "=SUMIF(ChiFi!R4C1:R10000C1,"">="" &MAX(RC[-1],R4C4),ChiFi!R4C5:R10000C5) -SUMIF(ChiFi!R4C1:R10000C1,"">="" & IF(R[1]C3="""",R5C4+1,R[1]C3),ChiFi!R4C5:R10000C5)"
        .Range(.[D8], [D65000].End(xlUp)).Value = .Range(.[D8], [D65000].End(xlUp)).Value
   End With
End Sub

 

[Kh Biet]

Bạn thử nhập từ ngày 31/01/2010 đến ngày 31/12/2010 xem Tháng 01/2010 kết quả bao nhiêu phí?

Cám ơn thày.
Nhà em đã sửa lại, thực ra đây là một hướng, thày và các bạn kiểm tra, nhà em chỉ thử theo hướng này thôi, coi nó là một phương án .

Đêm làm bài tập toét cả mắt, sáng vợ lại gọi dậy sớm, nhà em tức cảnh sinh tình :

GPE,GPE,lại GPE
Sáng ra vợ gọi, mắt cay sè
Thương ai, nhớ ai, mà không ngủ ?
Thương nhớ gì đâu...rặt CODE.

Cám ơn các thầy, cám ơn các bạn !

 

[Excel Công Cụ Tuyệt Vời Của Bạn]

Chuyến này chắc là không theo nổi bài này, bài tập đơn giản cho người bắt đầu mà khó quá, chắc em phải học lại lớp vỡ lòng.

 

[SA_DQ]

Bài tập 07: Thêm dòng tính tổng sau khi đã thống kê số liệu (Bài này dễ nè bạn.)

Chuyến này chắc là không theo nổi bài này, "Bài tập đơn giản cho người bắt đầu" mà khó quá, chắc em phải học lại lớp vỡ lòng.

Sau khi thống kê số liệu theo yêu cầu của các bài trước (Xem tại bài #27), ta cần thêm dòng tổng cộng, như sau

​

| A | B | C | D 1 ||Ngày bắt đầu:| 2/16/2012 |
2 ||Ngày Kết thúc:| 11/30/2012 |
3 ||||
4|TT | Tháng khảo sát | Chi fí | Ghi chú 5 |1| Tháng 02/2012 ||
6 |2| Tháng 03/2012 ||
7 |3| Tháng 04/2012 ||
8 |4| Tháng 05/2012 ||
9 |5| Tháng 06/2012 ||
|. .|. . . . . .||
|. .|. . . ||
|10| Tháng 11/2012 ||
||||
|| Tổng cộng: | ###.###.### |

Các bạn có thể giả lập số liệu từ [C5:C14] rồi viết macro theo iêu cầu của đề bài

'
'
'
'

 

[Excel Công Cụ Tuyệt Vời Của Bạn]

Em nghĩ thêm dòng tổng bên dưới thì

iEr = Sheet2.Range("C65000").End(xlUp).Row

With Sheet2.Range("B" & iEr)
.Offset(2, 0) = "T" & ChrW(7893) & "ng c" & ChrW(7897) & "ng:"
.Offset(2, 1).FormulaR1C1 = "=SUM(R[-" & iEr - 4 & "]C:R[-1]C)"
End With

 

[ChanhTQ@]

Có vài cách nào khác nữa không bạn?

Em nghĩ thêm dòng tổng bên dưới thì

​

(Nếu có thì xin các bạn khác tiếp tục nha)



​

 

[tvxdgt]

Bài 7: Đóng góp thêm một cách

Sub tong()
Set sw = Worksheets("TH")  
Sheets("TH").Activate          
 iRow = sw.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row          
 Range("c" & iRow + 1).Formula = "=SUM(C" & iRow & ":C" & (5) & ")"
End Sub

 

[Hong.Van]

Bài 7: Đóng góp thêm một cách

Sub tong()
Set sw = Worksheets("TH")  
Sheets("TH").Activate          
 iRow = sw.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row          
 Range("c" & iRow + 1).Formula = "=SUM(C" & iRow & ":C" & (5) & ")"
End Sub

Vui lòng giải thích rõ câu lệnh

[FONT=Courier New][COLOR=#0000bb]iRow [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000bb]sw[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000bb]Rows[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]Count[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000bb]3[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000bb]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000bb]xlUp[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000bb]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000bb]1[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000bb]0[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000bb]Row[/COLOR][/FONT]

Xin cảm ơn

 

[ndu96081631]

Vui lòng giải thích rõ câu lệnh

[FONT=Courier New][COLOR=#0000bb]iRow [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000bb]sw[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#ff0000]Rows.Count[/COLOR][COLOR=#007700], [/COLOR][COLOR=#ff0000]3[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000bb]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000bb]xlUp[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000bb]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000bb]1[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000bb]0[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000bb]Row[/COLOR][/FONT]

Xin cảm ơn

Rows.Count là Mút chỉ ở tận cùng bảng tính ---> Dòng cuối luôn
3 <==> Cột C
Cells(Rows.Count, 3) <===> Dòng cuối của cột C
sw.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row ---> Từ "địa ngục", bắn lên đến ô cuối cùng có dữ liệu, xong "dze đít" xuống 1 dòng
Tóm lại iRow chính là dưới dòng cuối cùng có dữ liệu của cột C đúng 1 dòng

 

[ChanhTQ@]

Đề nghị TVXDGT;456055 bở sung cụm từ "Tổng cộng" cái nha

Bài 7: Đóng góp thêm một cách

Gợi í:
(1) Có thể lấy từ ô đâu đó ở trang tính nào đó mà bỏ vô cái nha.

(2) Nghiên cứu macro này xem nó làm gì đây:

Option Explicit
Sub gpeName()
 Dim Ten
 For Each Ten In ThisWorkbook.Names
    If Mid(Ten.Value, 3, 1) = "T" And Mid(Ten.Value, 5, 2) = "ng" Then
        MsgBox Mid(Ten.Value, 3, 9):            Exit For
    End If
 Next Ten
End Sub

./.

 

[Hong.Van]

Rows.Count là Mút chỉ ở tận cùng bảng tính ---> Dòng cuối luôn
3 <==> Cột C
Cells(Rows.Count, 3) <===> Dòng cuối của cột C
sw.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row ---> Từ "địa ngục", bắn lên đến ô cuối cùng có dữ liệu, xong "dze đít" xuống 1 dòng
Tóm lại iRow chính là dưới dòng cuối cùng có dữ liệu của cột C đúng 1 dòng

iRow = sw.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
Nếu vậy thì có cần .Row không thầy?
Vì em thấy đã xác định được Cell rồi?
Em cảm ơn

 

[Hoàng Trọng Nghĩa]

Tình hình là em "cá Đuối" rồi. Có ai kéo giùm em lên với ạ.
Em mới làm quen thôi, các thầy và các anh chị dắt tay em từng bước một, hic, bước cao quá em ...bước hụt...

Đuối sức thì phục hồi, lỡ ăn "cá đuối" thì phải xực thêm "cá hồi" nữa là khỏe lại thôi.

iRow = sw.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
Nếu vậy thì có cần .Row không thầy?
Vì em thấy đã xác định được Cell rồi?
Em cảm ơn

Bạn đừng nhầm lẫn giữa địa chỉ ô (address) và hàng thứ (row) của nó chứ!

Khi bạn chọn, truy vấn một địa chỉ thì không phải thêm Row, nhưng nếu bạn muốn xác định địa chỉ ô đã chọn đó nằm ở hàng thứ mấy thì bạn lại cho "râu" vào.

 

[tvxdgt]

Gợi í:
(1) Có thể lấy từ ô đâu đó ở trang tính nào đó mà bỏ vô cái nha.
./.

Sub tong()
Set sw = Worksheets("TH")  
Sheets("TH").Activate         
  iRow = sw.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row        
   sw.Cells(iRow + 1, 2).Select   
 Selection.FormulaR1C1 = "=tongcong"   
 Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"    
Selection.Font.Bold = True    
With Selection.Font      
  .Color = -16776961        
.TintAndShade = 0   
 End With          
 Range("c" & iRow + 1).Formula = "=SUM(C" & iRow & ":C" & (5) & ")"
End Sub

 

[ChanhTQ@]

Gợi í cho những bạn chưa làm bài tập 7 đây nè:

Ta để sẵn "Tổng cọng" ở ô [B999] & công thức =Sum("C5:C997") ở [C999];

Như vậy ta chỉ việc viết vài dòng lệnh ẩn các hàng không cần thiết đi mà thôi;

Bạn nào thử xem sao(!)

 

[tvxdgt]

Ta để sẵn "Tổng cọng" ở ô [B999] & công thức =Sum("C5:C997") ở [C999];

Như vậy ta chỉ việc viết vài dòng lệnh ẩn các hàng không cần thiết đi mà thôi;

Bạn nào thử xem sao(!)

Sub tong2()
Application.ScreenUpdating = False
On Error Resume Next
Selection.AutoFilter
[B5:B997].SpecialCells(4).EntireRow.Hidden = True
Application.ScreenUpdating = True
Selection.AutoFilter
End Sub

 

[canhsatlangtu113]

Sửa lại bài 5.

Public Sub Xuan3()
Application.ScreenUpdating = False
Dim Rng As Range, Cll As Range, Dau As Long, Cuoi As Long, I As Long, Tem As Long, SoThang As Long, Thang As Long
With Sheets("ChiFi")
    Set Rng = .Range(.[A4], .[A65000].End(xlUp))
End With
With Sheets("sheet1")
.[A8:C1000].ClearContents
Dau = DateSerial(Year(.[C4]), Month(.[C4]), 1)
Cuoi = DateSerial(Year(.[C5]), Month(.[C5]), 1)
SoThang = DateDiff("m", Dau, Cuoi) + 1
For I = 1 To SoThang
    Thang = DateSerial(Year(Dau), Month(Dau) + I - 1, 1)
    .Cells(I + 7, 1).Value = I
    .Cells(I + 7, 2).Value = "Thang " & Format(Thang, "mm/yyyy")
    For Each Cll In Rng
        If Cll >= .[C4] And Cll <= .[C5] Then
                Tem = DateSerial(Year(Cll), Month(Cll), 1)
            If Tem = Thang Then
                .Cells(I + 7, 3).Value = .Cells(I + 7, 3).Value + Cll.Offset(, 4).Value
            End If
        End If
    Next
Next I
End With
Set Rng = Nothing
Application.ScreenUpdating = True
End Sub

Nàng thơ viết code....vừa làm thơ vừa viết code được chăng?
mà sao mình test thử code này không chạy nhỉ?

 

[Hoàng Trọng Nghĩa]

Sub tong2()
Application.ScreenUpdating = False
On Error Resume Next
Selection.AutoFilter
[B5:B997].SpecialCells(4).EntireRow.Hidden = True
Application.ScreenUpdating = True
Selection.AutoFilter
End Sub

Thủ tục này hơi lạ hen! Sao lại Selection.AutoFilter ở dòng đầu và dòng cuối nhỉ? Tác dụng của nó là gì và nó lọc cái gì vậy?

 

[Kh Biet]

Thủ tục này hơi lạ hen! Sao lại Selection.AutoFilter ở dòng đầu và dòng cuối nhỉ? Tác dụng của nó là gì và nó lọc cái gì vậy?

Tôi đã text thử code của bạn Tvxdgt, Selection.AutoFilter ở dòng cuối có tác dụng thực hiện lệnh nhưng không hiện Nút AutoFilter . Và phải thêm dòng lệnh Selection.EntireRow.Hidden = False trước đoạn code trên để trả lại AutoFilter trước khi thay đổi, nếu điều kiện sau nhiều dữ liệu hơn lần trước đó . các bạn kiểm tra, không biết có đúng không nữa .
Kèm theo tập tin

 

[Hoàng Trọng Nghĩa]

Tôi đã text thử code của bạn Tvxdgt, Selection.AutoFilter ở dòng cuối có tác dụng thực hiện lệnh nhưng không hiện Nút AutoFilter . Và phải thêm dòng lệnh Selection.EntireRow.Hidden = False trước đoạn code trên để trả lại AutoFilter trước khi thay đổi, nếu điều kiện sau nhiều dữ liệu hơn lần trước đó . các bạn kiểm tra, không biết có đúng không nữa .
Kèm theo tập tin

Trường hợp của bạn (gần như hợp lý):

    With Rows("8:998")[COLOR=#ff0000][B].Select[/B][/COLOR]
        Selection.EntireRow.Hidden = False
    End With

Tôi nói như vậy là vì bạn có chỗ để Select rồi từ đó mới Selection. Tức là đã xác định rõ vị trí đã chọn rồi thì cứ thế mà "quất" thôi. Còn thủ tục kia không có "nơi nương tựa" nên bạ đâu là "quất" đó, tại ngay cái ô đang Active/ select bất kỳ trên sheet.

Thủ tục trên của bạn lẽ ra chỉ cần như vầy:

    Rows("8:998").Select
    Selection.EntireRow.Hidden = False

Không cần quýt cam gì hết

Nhưng tốt hơn là như vầy:

    Rows("8:998").EntireRow.Hidden = False

Bạn thử sửa với những dạng "cam quýt" của toàn bộ thủ tục của bạn như tôi hướng dẫn xem có phải nhanh hơn và gọn hơn hay không!

 

[Kh Biet]

Cám ơn bạn Nghĩa, mình viết được thế đã đủ "tắc thở", Thấy nó chạy, không chống lại là mừng rồi . Học thêm được món bỏ "cam quýt" của bạn, chạy cho nhanh . Mình nhớ rồi ! xin cám ơn bạn .