Hỏi giải pháp sao cho Code trở thành Code động có tính "co dãn" cùng bảng tính

[Dauthivan]

Nhờ các thày và mọi người trên diễn đàn tận tình chỉ bảo, em đã bước đầu hiểu được các thuật toán VBA. Em làm thử nghiên cứu bài toán lọc vật tư trên diễn đàn có 1 số chỗ em chưa hiểu hết bản chất, em xin mạn phép được hỏi một chút ah, cụ thể: Đầu bài toán của em như sau:

Yêu cầu của bài toán để tổng hợp vật tư ra Sheet THVT bên cạnh, theo mẫu em xin post hình để mọi người dễ nhìn:

Code của bài toán như sau:

Sub TongVatTu()
    
Application.ScreenUpdating = False

''Loc lay vat tu
    Dim n As Long, m As Long
    m = S02.Range("C65000").End(xlUp).Row
    If m > 5 Then
        S02.Select
        Rows("6:" & m + 1).Select
        Selection.Delete Shift:=xlUp
    End If
    n = S01.Range("E65000").End(xlUp).Row
    For i = 6 To n
        If S01.Cells(i, 3) <> "công" And S01.Cells(i, 3) <> "ca" And S01.Cells(i, 3) <> "%" Then
            m = S02.Range("C65000").End(xlUp).Row
            DVT = S01.Cells(i, 3)
            If S01.Cells(i, 4) <> 0 Then
                S02.Cells(m + 1, 2) = S01.Cells(i, 2)
                S02.Cells(m + 1, 3) = DVT
                S02.Cells(m + 1, 7) = "=COUNTIF(R6C2:RC2,RC[-5])"
            End If
        End If
    Next
    
''Xoa Vat tu trung va sort theo ten vat tu
    m = S02.Range("G65000").End(xlUp).Row
    For i = m To 6 Step -1
        S02.Cells(i, 7).Select
        If S02.Cells(i, 7) > 1 Then
           Selection.EntireRow.Delete
        End If
    Next
    m = S02.Range("C65000").End(xlUp).Row
    Range("B6:C" & m).Select
    Selection.Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

''Lay tong so luong vat tu va tien cua tung vat tu
    n = S01.Range("E65000").End(xlUp).Row
    m = S02.Range("C65000").End(xlUp).Row
    Columns("G:G").Select
    Selection.ClearContents
    For i = 6 To m
        S02.Cells(i, 1) = i - 5
        S02.Cells(i, 4) = "=SUMIF('Bang Du Toan'!R6C2:R" & n & "C2,RC[-2],'Bang Du Toan'!R6C5:R" & n & "C5)"
        S02.Cells(i, 4).Select
        Selection.NumberFormat = "#,##0.000"
        S02.Cells(i, 6) = "=ROUND(RC[-2]*RC[-1],0)"
    Next
    
    Application.ScreenUpdating = True
    
End Sub

Em có 1 số điều chưa rõ, xin được chỉ giúp:

1) Em hiểu đoạn Columns("G:G").Select; Selection.ClearContents là xoá toàn bộ cột G ở cả 2 Sheet, nhưng em thử Test bằng cách đánh số vào cột G của Sheet Bangdutoan thì nó không bị xoá khi chạy.

2) Em không biết mọi người khi đánh đoạn S02.Cells(i, 4) = "=SUMIF('Bang Du Toan'!R6C2:R" & n & "C2,RC[-2],'Bang Du Toan'!R6C5:R" & n & "C5)" chẳng hạn, có quay về màn hình excel nhờ trợ giúp viết cụm 'Bang Du Toan'! ...cho chính xác hay không? hay là cứ đánh thủ công bình thường như đánh Word (chứ không lấy tham chiếu trực tiếp từ Excel)

3) Ý này em mượn bài này để hỏi chung cho mọi bài, tức là tình trạng Code không liên hệ gì với Bảng excel khi bảng Excel bị thêm bớt dòng cột ( ví dụ nếu chèn hoặc xoá bớt dòng ở 1 trong 2 Sheet trên) thì Tổng hợp sẽ không đúng nữa? Vậy có cách nào cho Code trở thành "Code động", kiểu như khai biến mảng động ý ah?

4) Em không hiểu tại sao Code chưa hoàn thiện ở điểm gì mà nếu em xoá phần kết quả vừa chạy ra (SheetTHVT) thì em phải ấn Click Code chạy phải 2 lần nó mới được?

 

[thanhlanh]

(nói thật, tôi chẳng khoái cái vụ chèn công thức vào code này tí nào....)

Nhiều khi không khoái nhưng cũng phải điền công thức vào để gởi file cho đối tác kiểm tra!

 

[Dauthivan]

Nhiều khi không khoái nhưng cũng phải điền công thức vào để gởi file cho đối tác kiểm tra!

Thưa các thày, cái khó là ở đây ah. Khi mặt bằng trình độ Excel nói chung của nơi chưa phải ở mức cao thì đối tác, sếp cứ bắt làm kiểu nào cũng được (Code không sao) ,nhưng trên màn hình nó phải được kiểm soát bằng các công thức đơn giản (Sum, sumif..hàm đơn giản thôi). Tức là Làm theo kiểu hiện đại (Code) mà KQ cuối cùng về mặt hình thức cứ phải giống như Sum bằng tay.

 

[trungvdb]

Làm thế nào để sắp xếp theo Tiếng Việt

Đoàn này:

''Xoa Vat tu trung va sort theo ten vat tu
    m = S02.Range("G65000").End(xlUp).Row
    For i = m To 6 Step -1
        S02.Cells(i, 7).Select
        If S02.Cells(i, 7) > 1 Then
           Selection.EntireRow.Delete
        End If
    Next
    m = S02.Range("C65000").End(xlUp).Row
    Range("B6:C" & m).Select
    Selection.Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

Em thấy việc sắp xếp vẫn chưa đúng chữ cái của Tiếng Việt, ví dụ hết chữ D rồi mới đến chữ Đ...

Xin mọi người gợi ý giúp cách sắp xếp theo chữ cái của Tiếng Việt.

 

[Dauthivan]

Bài này của bạn nếu giải quyết bằng Array + Dictionary mới là cách tốt nhất
Code như sau:

Sub TongHop()
  Dim sArray, Arr(), lR As Long, n As Long
  Dim tmp1 As String, tmp2 As String, tmp3 As Double
  sArray = S01.Range("A6:E1000").Value
  ReDim Arr(1 To UBound(sArray, 1), 1 To 4)
  With CreateObject("Scripting.Dictionary")
    For lR = 1 To UBound(sArray, 1)
      If sArray(lR, 2) <> "" Then
        If sArray(lR, 1) = "" Then
          If sArray(lR, 3) <> "công" And sArray(lR, 3) <> "ca" And sArray(lR, 3) <> "%" Then
            tmp1 = sArray(lR, 2)
            tmp2 = sArray(lR, 3)
            tmp3 = sArray(lR, 5)
            If Not .Exists(tmp1) Then ''<-- chổ này xét sự tồn tại để lấy Item duy nhất
              n = n + 1
              .Add tmp1, n
              Arr(n, 1) = n
              Arr(n, 2) = tmp1
              Arr(n, 3) = tmp2
              Arr(n, 4) = tmp3
            Else
              Arr(.Item(tmp1), 4) = Arr(.Item(tmp1), 4) + tmp3 ''<-- Nếu có Item trùng thì sẽ cộng dồn
            End If
          End If
        End If
      End If
    Next
    If n Then ''<-- Nếu n > 0 thì chứng tỏ các điều kiện thiết lập ở trên đã thỏa mản, ta mới tiến hành gán mảng xuống sheet
      S02.Range("A6:F1000").ClearContents
      S02.Range("A6").Resize(n, 4).Value = Arr
    End If
  End With
End Sub

Trong các bài toán liên quan đến lọc duy nhất thì tôi cho rằng dùng Dictionary là tốt nhất vì:
- Tốc độ rất cao
- Độ chính xác gần như tuyệt đối
- Code đơn giản nhất
Nếu bạn đồng ý với phương pháp này thì chúng ta sẽ bàn tiếp

Cho em hỏi tại sao sArray, Arr() cùng là khai báo kiểu mảng mà cấu trúc có vẻ khác nhau thế nhỉ: một cái có (), một cái không.
-------------------------
Em xin hỏi thêm: Bởi lẽ đúng quy định vậy thì cứ làm, tuy vậy em xin phép được nhờ tư vấn cách sử dụng dấu &, tại saolúc có, lúc không (kể cả CT Excel em rất hay nhầm lẫn, không biết khi nào có nó, khi nào không cần):

Cụ thể, em xin ví dụ dòng này
ActiveCell.FormulaR1C1 = "=SUM(R6C6:R" & m & "C6)"

Em thắc mắc tại sao không là ActiveCell.FormulaR1C1 = "=SUM(R6C6:R" m "C6)" (bỏ đi dấu & có vẻ thừa cho đỡ rắc rối).

 

[nmhung49]

Cho em hỏi tại sao sArray, Arr() cùng là khai báo kiểu mảng mà cấu trúc có vẻ khác nhau thế nhỉ: một cái có (), một cái không.

Cái này người ta khai báo để biết sArray là mảng để lấy dữ liệu từ ở sheet lên, con Arr() theo kiểu có () thì để biết đó là mảng động để chúng ta làm việc trên nó là chủ yếu nó chung là để Redim, hoăc Resverse gì đó má sArray khai báo vậy cũng có thể Redim, Resverse nữa mà ít ai làm,

Cụ thể, em xin ví dụ dòng này
ActiveCell.FormulaR1C1 = "=SUM(R6C6:R" & m & "C6)"
Em thắc mắc tại sao không là ActiveCell.FormulaR1C1 = "=SUM(R6C6:R" m "C6)" (bỏ đi dấu & có vẻ thừa cho đỡ rắc rối).

Nếu bạn không khai báo biến m cho dù để hay không để & cũng bị báo lỗi nếu khai báo rồi thì nó hiếu đó là biến mà muốn ghép cái gì đó thì phải dùng &, còn bạn không dùng dấu & thì tự động VBA cũng báo lỗi

 

[Dauthivan]

Nếu bạn không khai báo biến m cho dù để hay không để & cũng bị báo lỗi, nếu khai báo rồi thì nó hiếu đó là biến mà muốn ghép cái gì đó thì phải dùng &, còn bạn không dùng dấu & thì tự động VBA cũng báo lỗi

Cảm ơn bác, em chỉ hơi tò mò, thắc mắc là tại sao cái ông Microsoft lai nghĩ ra phức tạp thế làm gì để phải thêm công đánh mất thời gian, ý em là nếu bỏ nó đi tức là còn ActiveCell.FormulaR1C1 = "=SUM(R6C6:R" m "C6)"thì có phải gọn hơn không (có nhầm lẫn với trường hợp nào đâu mà sợ).

 

[ptm0412]

Cảm ơn bác, em chỉ hơi tò mò, thắc mắc là tại sao cái ông Microsoft lai nghĩ ra phức tạp thế làm gì để phải thêm công đánh mất thời gian, ý em là nếu bỏ nó đi tức là còn ActiveCell.FormulaR1C1 = "=SUM(R6C6:R" m "C6)"thì có phải gọn hơn không (có nhầm lẫn với trường hợp nào đâu mà sợ).

Bạn có khi nào thắc mắc tại sao chân tay nối với cơ thể lại cần khớp xương nối?
Hoặc có khi nào bạn thắc mắc tại sao bánh xe gắn vào khung xe lại phải có bù lon?
Hoặc có khi nào bạn thắc mắc tại sao cái ghế gỗ bạn ngồi lại phải có mộng hoặc phải đóng đinh?

Toàn những thứ "dư thừa" cả, sao không bỏ đi cho khoẻ?

 

[Dauthivan]

Bạn có khi nào thắc mắc tại sao chân tay nối với cơ thể lại cần khớp xương nối?
Hoặc có khi nào bạn thắc mắc tại sao bánh xe gắn vào khung xe lại phải có bù lon?
Hoặc có khi nào bạn thắc mắc tại sao cái ghế gỗ bạn ngồi lại phải có mộng hoặc phải đóng đinh?

Toàn những thứ "dư thừa" cả, sao không bỏ đi cho khoẻ?

Vâng, đúng là em chưa lắm rõ, em không hiểu được bản chất khi nào cần có nó, khi nào không cần...em chỉ muốn tìm hiểu liệu nó quy luật gì đó thôi (tức là em muốn tìm mẹo gì đó cho dễ nhớ nhanh để vận dụng cho đúng).

Em xin cảm ơn thày rất nhều, trong thời gian tới em sẽ cố gắng hơn.

 

[ndu96081631]

Cho em hỏi tại sao sArray, Arr() cùng là khai báo kiểu mảng mà cấu trúc có vẻ khác nhau thế nhỉ: một cái có (), một cái không.
-------------------------
Em xin hỏi thêm: Bởi lẽ đúng quy định vậy thì cứ làm, tuy vậy em xin phép được nhờ tư vấn cách sử dụng dấu &, tại saolúc có, lúc không (kể cả CT Excel em rất hay nhầm lẫn, không biết khi nào có nó, khi nào không cần):

Cụ thể, em xin ví dụ dòng này
ActiveCell.FormulaR1C1 = "=SUM(R6C6:R" & m & "C6)"

Em thắc mắc tại sao không là ActiveCell.FormulaR1C1 = "=SUM(R6C6:R" m "C6)" (bỏ đi dấu & có vẻ thừa cho đỡ rắc rối).

1> Khai báo sArray tương đương với Dim sArray as Variant. Nó có thể là mảng, có thể không phải ----> Sau khi gán sArray = Range("gì gì đó").Value thì sArray mới thật sự trở thành mảng ---> Mảng này được tạo từ việc gán dữ liệu của mảng khác vào... Nếu khai báo sArray() để nó luôn là mảng sẽ có trường hợp bị sai, chẳng hạn khi Range("gì gì đó") là 1 cell
2> Khai báo Arr() thì chắc chắn Arr là mảng
3> Lấy ví dụ để minh họa:
- Cell A1 có giá trị = 15
- Cell B1 bạn muốn có kết quả Ngày 15, tức nối chữ "Ngày" với số 15 ở A1, vậy bạn làm sao? Có phải B1 = "Ngày " & A1 hay không? Vậy nếu bỏ dấu & thì sẽ.. thế nào đây?