Nhờ mọi người kiểm tra đoạn code tính tổng có điều kiện viết VBA sai chỗ nào? (1 người xem)

[khoavu87]

Khi làm một vấn đề em có vướng mắc mà chưa làm ra đc. mong mọi người giúp đỡ. có yêu cầu như file đính kèm.

 

[Hai Lúa Miền Tây]

Khi làm một vấn đề em có vướng mắc mà chưa làm ra đc. mong mọi người giúp đỡ. có yêu cầu như file đính kèm.

Vài lời góp ý:
-Sao không dùng sumif khỏi dùng vòng lặp ?
-ActiveCell chứ không phải ActivateCell nhe bạn.
-Còn nếu vẫn giữ lặp trường như cũ thì xem file nhé

 

[ptm0412]

Lỗi 1: Trên đời không có Activatecell, chỉ có ActiveCell thôi
Lỗi 2: Gặp "dung" thì tính, tính xong phải thoát ra, không thoát nó tính tiếp cho đến hết (B15)
Lỗi 3: Nếu không gặp dung thì tính Sum, tại sao chỉ Sum 1 cell?

Else
Range("c1").Value = Application.WorksheetFunction.Sum(rng.Offset(, -1))

Kết quả là Sum 1 ô cuối cùng (B15)

Sub tinh()
Dim rng As Range
For Each rng In ActiveSheet.Range("b2:b15")
  rng.Offset(, -1).Activate
  If rng.Value = "dung" Then
    Range("c1") = Application.WorksheetFunction.Sum(Range([A2], ActiveCell))
    Exit For
  Else
    Range("c1") = Application.WorksheetFunction.Sum(Range([A2], ActiveCell))
  End If
Next
End Sub

hoặc:

Sub tinh()
Dim rng As Range
For Each rng In ActiveSheet.Range("b2:b15")
    rng.Offset(, -1).Activate
    Range("c1") = Application.WorksheetFunction.Sum(Range([A2], ActiveCell))
    If rng.Value = "dung" Then Exit For
Next
End Sub

Tuy nhiên, For không bao giờ là tối ưu, hơn nữa, mỗi vòng lặp mỗi tính, mỗi lần tính 1 lần cho hiện ra kết quả, lại càng tệ.

 

[Hai Lúa Miền Tây]

Theo em thì dùng SumIf thì chắc ăn.
Nếu trường hợp trong vùng điều kiện có nhiều cell dung thì code trên chỉ cộng đến cell đầu tiên rồi thoát, không cộng nữa.

 

[ptm0412]

Dom nói SumIf, vậy làm thử 1 cái SumIf bằng công thức thường xem? (công thức hoặc VBA, khỏi file)
Còn nhiều hơn 1 "dung" thì không thấy tác giả nói tới.

 

[khoavu87]

Theo em thì dùng SumIf thì chắc ăn.
Nếu trường hợp trong vùng điều kiện có nhiều cell dung thì code trên chỉ cộng đến cell đầu tiên rồi thoát, không cộng nữa.

Anh Dom cho em luôn đoạn code dùng sumif để em học thêm ạ.
PTM : Sư phụ ơi bài này còn cách nào mà sư phụ thấy ưng ý nhất sư phụ cho em luôn nhé, em cần nhiều code càng tốt để học hỏi nhiều hơn nữa ạ.
Em chân thành cảm ơn các bác.
ko cần bác ạ. chỉ một dừng thui.

 

[Hai Lúa Miền Tây]

Dom nói SumIf, vậy làm thử 1 cái SumIf bằng công thức thường xem? (công thức hoặc VBA, khỏi file)
Còn nhiều hơn 1 "dung" thì không thấy tác giả nói tới.

Hix.... Em xin lỗi, Không thể dùng SumIf, do em lẩm cẩm, xem dữ liệu rồi nảy ra ý định đó.

 

[ptm0412]

Bài trên là 2 code, sau đây 2 code nữa, theo thứ tự cái sau tốt hơn cái trước:

Sub tinh()
Dim rng As Range, Tmp As Double
For Each rng In ActiveSheet.Range("b1:b15")
    Tmp = Tmp + rng.offset(0, -1)
    If rng.Value = "dung" Then Exit For
Next
   [C1] = Tmp
End Sub

Sub tinh2()
Set Found = Range("b2:b15").Find(What:="dung")
If Not Found Is Nothing Then
    Found.Offset(, -1).Activate
    [C1] = Application.Sum(Range([A2], ActiveCell))
Else
    [C1] = Application.Sum(Range("A2:A15"))
End If
End Sub

 

[Hai Lúa Miền Tây]

Dùng code sau khỏi dùng vòng lặp, nếu như xác định dung đầu tiên

Sub tinh()
Dim c As Range
With Range("b2:b16")
    Set c = .Find("dung")
    If Not c Is Nothing Then
         c.Offset(0, -1).Activate
          Range("c1") = Application.WorksheetFunction.Sum(Range([A2], ActiveCell))
        Else
          Range("c1") = Application.WorksheetFunction.Sum(Range("A2:A16"))
    End If
End With

End Sub

 

[sealand]

Với bài này mình tham gia như sau:
-Dùng hàm, không dùng sub. Khi dữ liệu thay đổi thì sheet tự động tính toán lại, không phải ra lệnh.
-Mình viết lại hàm UDF cho hợp lý hơn (Bỏ vòng lặp):

Function Cong(Rg As Range)
With Application.WorksheetFunction
If .CountIf(Rg.Columns(2), "dung") = 0 Then
Cong = .Sum(Rg.Columns(1))
Else
Cong = .Sum(Rg.Cells(1, 1).Resize(.Match("dung", Rg.Columns(2), 0)))
End If
End With
End Function

Tại C1=Cong(A2:B15)

-Có thể dùng Name cũng được:
Đặt Name data=OFFSET(Sheet1!$A$2,,,IF(iserror(MATCH("dung",Sheet1!$B$2:$B$16,0)),15,MATCH("dung",Sheet1!$B$2:$B$16,0)),)

Tại C1=Sum(data)

 

[ptm0412]

Sorry nha, đoạn code thứ 4 xài không được, vì chưa loại trừ việc tìm không thấy. Sẽ sửa ngay, code của Dom xài được đó.

 

[ndu96081631]

Bài trên là 2 code, sau đây 2 code nữa, theo thứ tự cái sau tốt hơn cái trước:

Sub tinh2()
Set Found = Range("b1:b15").Find(What:="dung")
If Not Found Is Nothing Then
    Found.Offset(, -1).Activate
    [C1] = Application.Sum(Range([A2], ActiveCell.Offset(, -1)))
Else
    [C1] = Application.Sum(Range("A2:A15"))
End If
End Sub

Hình như phải vầy chứ sư phụ
[C1] = Application.Sum(Range([A2], ActiveCell))
Tuy nhiên, theo em thì dùng MATCH sẽ chắc ăn hơn Find ---> Thử trong trường hợp cột B có 2 giá trị "dung" và chữ "dung" đầu tiên nằm tại B1 thì code sẽ sai ngay

 

[concogia]

Đang tập tành viết thấy bài này vui, xin góp một cách "bình dân học vụ" bảo đảm bạn Khoavu đọc sẽ hiểu ngay
Kiểm tra kết quả ở D1

Public Sub titi()
    Dim cl, vung As Range, i As Integer, kq
    Set vung = Range("b2:b16")
        If Application.WorksheetFunction.CountIf(vung, "dung") > 0 Then
        i = Application.WorksheetFunction.Match("dung", vung, 0)
            kq = Application.WorksheetFunction.Sum(Range("a2:a" & i + 1))
            Else
            kq = Application.WorksheetFunction.Sum(Range("a2:a16"))
        End If
Range("d1") = kq
End Sub

 

[ptm0412]

Hình như phải vầy chứ sư phụ
[C1] = Application.Sum(Range([A2], ActiveCell))
Tuy nhiên, theo em thì dùng MATCH sẽ chắc ăn hơn Find ---> Thử trong trường hợp cột B có 2 giá trị "dung" và chữ "dung" đầu tiên nằm tại B1 thì code sẽ sai ngay

Đúng rồi ndu ui. Đáng lẽ code sai thì xoá, xoá lại tiếc nên ngồi gõ lại trực tiếp nên sai:
- Offset 2 lần
- Set Found = Range("b2:b15").Find(What:="dung") mới đúng

 

[ndu96081631]

Đang tập tành viết thấy bài này vui, xin góp một cách "bình dân học vụ" bảo đảm bạn Khoavu đọc sẽ hiểu ngay
Kiểm tra kết quả ở D1

Public Sub titi()
    Dim cl, vung As Range, i As Integer, kq
    Set vung = Range("b2:b16")
        If Application.WorksheetFunction.CountIf(vung, "dung") > 0 Then
        i = Application.WorksheetFunction.Match("dung", vung, 0)
            kq = Application.WorksheetFunction.Sum(Range("a2:a" & i + 1))
            Else
            kq = Application.WorksheetFunction.Sum(Range("a2:a16"))
        End If
Range("d1") = kq
End Sub

Anh không cần phải COUNTIF đâu!
Chỉ cần tính i nhờ vào hàm MATCH, nếu MATCH không tìm thấy thì i sẽ = 0 (đương nhiên phải có dòng On Error Resume Next ở trên đầu)
Em sửa code của anh lại thành:

Public Sub titi()
  Dim i As Long
  On Error Resume Next
  With Range("B2:B16")
    i = WorksheetFunction.Match("dung", .Cells, 0)
    If i = 0 Then i = .Rows.Count
    Range("d1") = WorksheetFunction.Sum(Range("a2").Resize(i))
  End With
End Sub

Tuy nhiên, bài này chỉ dùng công thức đã quá đủ, dùng code VBA hơi phí

 

[khoavu87]

thế này sư phụ nhỉ? hôm nay được các sư phụ trao đổi nên thấy vui quá. được học thêm rất nhiều.

Public Sub titi()
Dim cl, vung As Range, i As Integer, kq
Set vung = Range("b2:b16")
On Error Resume Next
i = Application.WorksheetFunction.Match("dung", vung, 0)
If i > 0 Then
kq = Application.WorksheetFunction.Sum(Range("a2:a" & i + 1))
Else
kq = Application.WorksheetFunction.Sum(Range("a2:a16"))
End If
Range("d1") = kq
End Sub

 

[ndu96081631]

thế này sư phụ nhỉ? hôm nay được các sư phụ trao đổi nên thấy vui quá. được học thêm rất nhiều.

OK đấy! Nhưng khoavu cố gắng giảm bớt các biến nếu có thể
Cứ tưởng tượng code có vài chục biến, khi kiểm tra cũng thấy rối mắt
Chơi kiểu kq = Application.WorksheetFunction.Sum(Range("a2:a" & i + 1)) này rất dở ---> Lở như ta tính từ A3 trở đi thì chẳng lẽ bạn phải sửa code thành kq = Application.WorksheetFunction.Sum(Range("a2:a" & i + 2)) sao? ---> Dùng Resize như tôi làm ở trên sẽ tổng quát hơn

 

[ChanhTQ@]

Góp thêm 1 hàm tự t5o

Option Explicit
Function TongFor(LookUpRange As Range)
 Dim sRng As Range
 
 Set sRng = LookUpRange.Find("dung", , xlFormulas, xlWhole)
 With Application.WorksheetFunction
   If Not sRng Is Nothing Then
      TongFor = .Sum(LookUpRange.Cells(1, 1).Offset(, -1).Resize(sRng.Row - 1))
   Else
      TongFor = .Sum(LookUpRange.Offset(, -1))
   End If
 End With
End Function

Do dịch chữ 'Dung" sang tiếng Việt có dầu là "Dừng", nên sẽ không có lặp lại chữ này fía dưới nó;

Cú fáp hàm tại ô trống nào đó sẽ là: =TongFor(B2:B16)

 

[concogia]

OK đấy! Nhưng khoavu cố gắng giảm bớt các biến nếu có thể
Cứ tưởng tượng code có vài chục biến, khi kiểm tra cũng thấy rối mắt
Chơi kiểu kq = Application.WorksheetFunction.Sum(Range("a2:a" & i + 1)) này rất dở ---> Lở như ta tính từ A3 trở đi thì chẳng lẽ bạn phải sửa code thành kq = Application.WorksheetFunction.Sum(Range("a2:a" & i + 2)) sao? ---> Dùng Resize như tôi làm ở trên sẽ tổng quát hơn

Mới đi Bình Dương về, tưng tưng
Thầy Ndu ơi, Thầy "hổng" thấy trong bài mình đã nói đây là code "bình dân học vụ" sao, tức là nghĩ sao làm vậy, chủ yếu góp thêm phần "súc tích" trong topic này thôi, miễn bạn Khoavu hiểu là mình "phái" rồi
Hihi, ai biểu làm từ A2, chứ nếu làm từ A1 thì đâu bị phát hiện (lỗi này thuộc về bạn Khoavu (mình chỉ chịu một tí)

 

[sealand]

Ta tiến hành dọn dẹp code của KhoaVu 1 chút để code chuẩn hơn, ý kiến mình thế này:

Public Sub titi()
Dim vung As Range
Set vung = Range("a2:b16")
With Application.WorksheetFunction
If .CountIf(vung, "dung") = 0 Then
Sheet1.[d1] = .Sum(vung.Columns(1))
Else
Sheet1.[d1] = .Sum(vung.Cells(1, 1).Resize(.Match("dung", vung.Columns(2), 0)))
End If: End With: Set vung = Nothing
End Sub