Lỗi code hàm CountA không biết nguyên nhân (mong giúp đỡ) (1 người xem)

[Blad01]

Em không biết code 2 lỗi ở đâu mà hàm CountA không hoạt động, mong anh chị giúp đỡ.

Code 1 em viết tường minh như sau, hàm CountA hoạt động bình thường:

With Sheet0
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 6) = WorksheetFunction.CountA(.Range("$CR$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 6).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 7) = WorksheetFunction.CountA(.Range("$CS$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 7).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 8) = WorksheetFunction.CountA(.Range("$CT$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 8).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 9) = WorksheetFunction.CountA(.Range("$CU$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 9).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 10) = WorksheetFunction.CountA(.Range("$CV$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 10).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 11) = WorksheetFunction.CountA(.Range("$CW$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 11).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 12) = WorksheetFunction.CountA(.Range("$CX$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 12).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 13) = WorksheetFunction.CountA(.Range("$CY$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 13).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 14) = WorksheetFunction.CountA(.Range("$CZ$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 14).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 15) = WorksheetFunction.CountA(.Range("$DA$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 15).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 16) = WorksheetFunction.CountA(.Range("$DB$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 16).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 17) = WorksheetFunction.CountA(.Range("$DC$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 17).Address))
    End With

Code 2 em rút gọn như sau, nhưng hàm CountA trả về là ô trống (không hoạt động, không báo lỗi):

  Dim k As WorksheetFunction, a
      k = CountA

      For j = 6 To 17

          a = Sheet0.[CL1].Offset(Sheet0.[CL1].End(xlDown).Row + 1, j)

          a = k(Sheet0.Range("$CK$2:" & a.Address))
      Next j

Mong anh chị giúp đỡ cách khắc phục. Em chân thành cảm ơn.

 

[befaint]

Sub Blad01()
Dim a As Range, r As Long, KQ As Long, j As Byte
For j = 6 To 17
    r = Sheet0.[CL1].Offset(Sheet0.[CL1].End(xlDown).Row + 1, 0).Row
    Set a = Sheet0.Range("CK2:CK" & r).Resize(, j)
    KQ = WorksheetFunction.CountA(a)
    MsgBox KQ
Next j
End Sub

 

[Blad01]

Cảm ơn bác đã giúp đỡ. Nhưng không hiểu sao Hàn countA vẫn không hoạt động, msgbox báo rỗng.

 

[befaint]

Cảm ơn bác đã giúp đỡ. Nhưng không hiểu sao Hàn countA vẫn không hoạt động, msgbox báo rỗng.

Thử lại code bài #2 xem...

 

[huuthang_bd]

Em không biết code 2 lỗi ở đâu mà hàm CountA không hoạt động, mong anh chị giúp đỡ.

Code 1 em viết tường minh như sau, hàm CountA hoạt động bình thường:

With Sheet0
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 6) = WorksheetFunction.CountA(.Range("$CR$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 6).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 7) = WorksheetFunction.CountA(.Range("$CS$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 7).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 8) = WorksheetFunction.CountA(.Range("$CT$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 8).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 9) = WorksheetFunction.CountA(.Range("$CU$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 9).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 10) = WorksheetFunction.CountA(.Range("$CV$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 10).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 11) = WorksheetFunction.CountA(.Range("$CW$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 11).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 12) = WorksheetFunction.CountA(.Range("$CX$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 12).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 13) = WorksheetFunction.CountA(.Range("$CY$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 13).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 14) = WorksheetFunction.CountA(.Range("$CZ$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 14).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 15) = WorksheetFunction.CountA(.Range("$DA$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 15).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 16) = WorksheetFunction.CountA(.Range("$DB$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 16).Address))
        .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 17) = WorksheetFunction.CountA(.Range("$DC$2:" & .[CL1].Offset(.[CL1].End(xlDown).Row + 1, 17).Address))
    End With

Code 2 em rút gọn như sau, nhưng hàm CountA trả về là ô trống (không hoạt động, không báo lỗi):

  Dim k As WorksheetFunction, a
      k = CountA

      For j = 6 To 17

          a = Sheet0.[CL1].Offset(Sheet0.[CL1].End(xlDown).Row + 1, j)

          a = k(Sheet0.Range("$CK$2:" & a.Address))
      Next j

Mong anh chị giúp đỡ cách khắc phục. Em chân thành cảm ơn.

Bạn thử sửa thành vầy xem

Sub ABC()
With Sheet0.Range("CL1").End(xlDown).Offset(1, 6).Resize(, 12)
    .FormulaR1C1 = "=CountA(R2C:R[-1]C)"
    .Value = .Value
End With
End Sub