Viết dùm em code cho nút cập nhât số liệu vào bảng CDPS (1 người xem)

  • Thread starter Thread starter yome
  • Ngày gửi Ngày gửi
Liên hệ QC

Người dùng đang xem chủ đề này

yome

GPE thật tuyệt vời
Tham gia
9/5/08
Bài viết
347
Được thích
113
Anh/chị viết dùm em code cập nhật dữ liệu sang bảng cân đối phát sinh.
(Câu hỏi cụ thể trong file đính kèm)
 

File đính kèm

Các anh/chị giúp em với nhé!
 
Upvote 0
Cái này em sử dụng hàm sumif là được rồi đâu cần phải ghi code làm chi.Em xem lại số dư đầu kỳ các tài khoản nó bị trùng số liệu đó.
 
Upvote 0
Cái này em sử dụng hàm sumif là được rồi đâu cần phải ghi code làm chi.Em xem lại số dư đầu kỳ các tài khoản nó bị trùng số liệu đó.

Vâng. Nếu dùng công thức thì em cũng làm được rồi. Nhưng file nó nặng anh ah. Vì vậy, em muốn nhờ mọi người viết dùm em code cho nó chạy nhẹ.
 
Upvote 0
Vậy em hay ghi Macro trong quá trình em lập công thức ,sau đó gán macro vừa tạo vào nút cập nhật,anh thấy sử dụng hàm sumif thì file cũng đâu có nặng lắm đâu.
 
Upvote 0
Sửa dùm code cập nhật sang bảng CĐPS!

Em đã ghi Marco rồi. Em nhờ anh/chị sửa dùm em đoạn code cho gọn lại!

PHP:
Sub taoso2()
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(DATA!RC[1]:R[42]C[1],CDPS!RC[-4],DATA!RC[3]:R[42]C[3])"
    Range("E12").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(DATA!R12C6:R54C6,CDPS!RC[-4],DATA!R12C8:R54C8)"
    Range("F12").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(DATA!RC[1]:R[42]C[1],CDPS!RC[-5],DATA!RC[3]:R[42]C[3])"
    Range("F12").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(DATA!R12C7:R54C7,CDPS!RC[-5],DATA!R12C9:R54C9)"
    Range("G12").Select
    ActiveCell.FormulaR1C1 = "=MAX(RC[-4]+RC[-2]-RC[-3]-RC[-1],0)"
    Range("H12").Select
    ActiveCell.FormulaR1C1 = "=MAX(RC[-4]+RC[-2]-RC[-5]-RC[-3],0)"
    Range("E12:H12").Select
    Selection.Copy
    Range("E12:H76").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 51
End Sub

(Kèm file gốc)
 

File đính kèm

Upvote 0
Bạn hãy thay thế macro trên theo taoso2 tôi làm dưới đây:

Mã:
Sub taoso2()
[COLOR="SeaGreen"]    'Nhan cong thuc cho tung cot C, D,...[/COLOR]
    Range("C12").Formula = "=VLOOKUP(A12,DMTK20,4,0)"
    Range("D12").Formula = "=VLOOKUP(A12,DMTK20,5,0)"
    Range("E12").Formula = "=SUMIF(DATA!$F$12:$F$54,CDPS!A12,DATA!$H$12:$H$54)"
    Range("F12").Formula = "=SUMIF(DATA!$G$12:$G$54,CDPS!A12,DATA!$I$12:$I$54)"
    Range("G12").Formula = "=MAX(C12+E12-D12-F12,0)"
    Range("H12").Formula = "=MAX(D12+F12-C12-E12,0)"
    
    Dim LastRow As Long
[COLOR="SeaGreen"]    'Nhan dong cuoi theo tai khoan o cot A[/COLOR]
    LastRow = Range("A65536").End(xlUp).Row
[COLOR="SeaGreen"]    'Copy cong thuc cho vung C12:H####[/COLOR]
    Range("C12:H12").Copy Range("C12:H" & LastRow)
[COLOR="SeaGreen"]    'Xoa cong thuc de dam bao toc do cua file Excel[/COLOR]
    Range("C12:H" & LastRow).Value = Range("C12:H" & LastRow).Value
End Sub
 
Upvote 0
Bạn hãy thay thế macro trên theo taoso2 tôi làm dưới đây:

Mã:
Sub taoso2()
[COLOR=seagreen]'Nhan cong thuc cho tung cot C, D,...[/COLOR]
Range("C12").Formula = "=VLOOKUP(A12,DMTK20,4,0)"
Range("D12").Formula = "=VLOOKUP(A12,DMTK20,5,0)"
Range("E12").Formula = "=SUMIF(DATA!$F$12:$F$54,CDPS!A12,DATA!$H$12:$H$54)"
Range("F12").Formula = "=SUMIF(DATA!$G$12:$G$54,CDPS!A12,DATA!$I$12:$I$54)"
Range("G12").Formula = "=MAX(C12+E12-D12-F12,0)"
Range("H12").Formula = "=MAX(D12+F12-C12-E12,0)"
 
Dim LastRow As Long
[COLOR=seagreen]'Nhan dong cuoi theo tai khoan o cot A[/COLOR]
LastRow = Range("A65536").End(xlUp).Row
[COLOR=seagreen]'Copy cong thuc cho vung C12:H####[/COLOR]
Range("C12:H12").Copy Range("C12:H" & LastRow)
[COLOR=seagreen]'Xoa cong thuc de dam bao toc do cua file Excel[/COLOR]
Range("C12:H" & LastRow).Value = Range("C12:H" & LastRow).Value
End Sub

Anh Tuân ơi, em làm được rồi. Nhưng có 1 vấn đề này em muốn hỏi thêm anh Tuân:
Nếu dữ liệu ở cột Dư đầu kỳ và cột Phát sinh không có phát sinh thì nó sẽ không hiện dòng đó nữa. Nếu 1 trong 2 cột đó mà phát sinh thì vân hiện dòng đó. Như vậy nhìn mới đẹp ah!
Anh Tuân xem lại và sửa dùm em nhé!

Mà sao em sửa:

PHP:
Range("E12").Formula = "=SUMIF(DATA!$F$12:$F$54,CDPS!A12,DATA!$H$12:$H$54)"
Range("F12").Formula = "=SUMIF(DATA!$G$12:$G$54,CDPS!A12,DATA!$I$12:$I$54)"

Thành như sau:

PHP:
Range("E12").Formula = "=SUMIF(DATA!$F$12:$F$54,CDPS!A12&"*",DATA!$H$12:$H$54)"
Range("F12").Formula = "=SUMIF(DATA!$G$12:$G$54,CDPS!A12&"*",DATA!$I$12:$I$54)"
Tại sao lại không được. Như vậy đã sai chỗ nào hả anh?
Anh chỉ dùm em nhé!
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
Giải quyết vấn đề thứ hai cho bạn bằng macro như sau

PHP:
Option Explicit
Sub HiddenBlankRows()
 Dim eRw As Long, jJ As Byte:          Dim WF, Cls As Range
 
 eRw = [B65500].End(xlUp).Row
 Application.ScreenUpdating = False
 Set WF = Application.WorksheetFunction
 For Each Cls In [C12].Resize(eRw)
   If WF.CountIf(Cls.Resize(, 4), "<>0") = 0 Then
      Cls.EntireRow.Hidden = True
   End If
 Next Cls
End Sub

(*) Có thể có cách khác ngắn hơn vòng lặp; Nhưng mình còn ít thời gian quá. Hẹn lúc khác vậy.
 
Upvote 0
Tạo bảng CDPS

Anh Tuân ơi, em làm được rồi. Nhưng có 1 vấn đề này em muốn hỏi thêm anh Tuân:
Nếu dữ liệu ở cột Dư đầu kỳ và cột Phát sinh không có phát sinh thì nó sẽ không hiện dòng đó nữa. Nếu 1 trong 2 cột đó mà phát sinh thì vân hiện dòng đó. Như vậy nhìn mới đẹp ah!
Anh Tuân xem lại và sửa dùm em nhé!

Mã:
Sub taoso2() 
  [COLOR="SeaGreen"] 'Nhan cong thuc cho tung cot C, D,...[/COLOR]
   Range("C12").Formula = "=VLOOKUP(A12,DMTK20,4,0)" 
   Range("D12").Formula = "=VLOOKUP(A12,DMTK20,5,0)" 
   Range("E12").Formula = "=SUMIF(DATA!$F$12:$F$54,CDPS!A12,DATA!$H$12:$H$54)" 
   Range("F12").Formula = "=SUMIF(DATA!$G$12:$G$54,CDPS!A12,DATA!$I$12:$I$54)" 
   Range("G12").Formula = "=MAX(C12+E12-D12-F12,0)" 
   Range("H12").Formula = "=MAX(D12+F12-C12-E12,0)" 
  [COLOR="SeaGreen"] 'Dong co so tien thi cho so 1 tai cot I[/COLOR]
   Range("I12").Formula = "=IF(SUM(C12:H12)<>0,1,0)" 
   Dim LastRow As Long 
   Dim rng As Range 
  [COLOR="SeaGreen"] 'Nhan dong cuoi theo tai khoan o cot A[/COLOR]
   LastRow = Range("A65536").End(xlUp).Row 
   Set rng = Range("C12:I" & LastRow) 
  [COLOR="SeaGreen"] 'Copy cong thuc tai C12:I12 cho vung C12:I####[/COLOR]
   rng.AutoFilter [COLOR="SeaGreen"] 'Xoa Filter. RAT QUAN TRONG!!![/COLOR]
   Range("C12:I12").Copy rng 
   [COLOR="SeaGreen"]'Xoa cong thuc de dam bao toc do cua file Excel[/COLOR]
   rng.Value = rng.Value 
   [COLOR="SeaGreen"]'Nhan doi dia chi vung tu C12:I(n) thanh C11:I(n)[/COLOR]
   Set rng = rng.Resize(rng.Rows.Count + 1).Offset(-1) 
  [COLOR="SeaGreen"] 'AutoFilter, loc lay gia tri 1 o cot I[/COLOR]
   rng.AutoFilter 7, 1 
  [COLOR="SeaGreen"] 'An cot loc I (Trong vung C:I, Cot I voi ColumnIndex = 7[/COLOR]
   rng.Columns(7).Hidden = True 
   Set rng = Nothing 
End Sub

Bạn tải file gửi kèm chạy thử xem. Các vấn đề khác cần bổ sung bạn cố tìm cách nhé!
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
Em đã ghi Marco rồi. Em nhờ anh/chị sửa dùm em đoạn code cho gọn lại!

PHP:
Sub taoso2()
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(DATA!RC[1]:R[42]C[1],CDPS!RC[-4],DATA!RC[3]:R[42]C[3])"
    Range("E12").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(DATA!R12C6:R54C6,CDPS!RC[-4],DATA!R12C8:R54C8)"
    Range("F12").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(DATA!RC[1]:R[42]C[1],CDPS!RC[-5],DATA!RC[3]:R[42]C[3])"
    Range("F12").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(DATA!R12C7:R54C7,CDPS!RC[-5],DATA!R12C9:R54C9)"
    Range("G12").Select
    ActiveCell.FormulaR1C1 = "=MAX(RC[-4]+RC[-2]-RC[-3]-RC[-1],0)"
    Range("H12").Select
    ActiveCell.FormulaR1C1 = "=MAX(RC[-4]+RC[-2]-RC[-5]-RC[-3],0)"
    Range("E12:H12").Select
    Selection.Copy
    Range("E12:H76").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 51
End Sub

(Kèm file gốc)

Rất vui vì yome có bài viết này. Bạn đã tự record macro và khi không hiểu thì post bài hỏi. Đây chắc chắn sẽ là một cách học nhanh nhất từ GPE
Qua những bài viết trả lời, chắc chắn bạn sẽ hoc được nhiều để bổ sung kiến thức cho mình
Chúc vui và cố gắng phát huy
Thân mến
XuanThanh
 
Upvote 0
Làm vầy có phải là gọn hơn không:
PHP:
Sub taoso2()
  With Range([A12], [A65536].End(xlUp))
    .Offset(, 4).Value = "=SUMIF(DATA!R12C6:R54C6,CDPS!RC[-4],DATA!R12C8:R54C8)"
    .Offset(, 5).Value = "=SUMIF(DATA!R12C7:R54C7,CDPS!RC[-5],DATA!R12C9:R54C9)"
    .Offset(, 6).Value = "=MAX(RC[-4]+RC[-2]-RC[-3]-RC[-1],0)"
    .Offset(, 7).Value = "=MAX(RC[-4]+RC[-2]-RC[-5]-RC[-3],0)"
    With .Offset(, 4).Resize(, 4): .Value = .Value: End With
  End With
End Sub
 
Upvote 0
Làm vầy có phải là gọn hơn không:
PHP:
Sub taoso2()
  With Range([A12], [A65536].End(xlUp))
    .Offset(, 4).Value = "=SUMIF(DATA!R12C6:R54C6,CDPS!RC[-4],DATA!R12C8:R54C8)"
    .Offset(, 5).Value = "=SUMIF(DATA!R12C7:R54C7,CDPS!RC[-5],DATA!R12C9:R54C9)"
    .Offset(, 6).Value = "=MAX(RC[-4]+RC[-2]-RC[-3]-RC[-1],0)"
    .Offset(, 7).Value = "=MAX(RC[-4]+RC[-2]-RC[-5]-RC[-3],0)"
    With .Offset(, 4).Resize(, 4): .Value = .Value: End With
  End With
End Sub
Nếu có thể NDU làm 1 code = Dic thử có nhanh hơn.
Cám ơn NDU nhiều.
 
Upvote 0
Nếu có thể NDU làm 1 code = Dic thử có nhanh hơn.
Cám ơn NDU nhiều.
Vâng! Tôi cũng tin là vậy!
Nhưng vì ở đây tác giả là người đang học hỏi nên tôi chỉ sửa lại những gì bạn ấy record macro được mà thôi!
(Làm quá người ta... ngợp, sẽ rất khó học hỏi ---> Với người mới, chỉ nội cái With... End With cũng đủ gây lúng túng rồi)
 
Upvote 0
Làm vầy có phải là gọn hơn không:
PHP:
Sub taoso2()
  With Range([A12], [A65536].End(xlUp))
    .Offset(, 4).Value = "=SUMIF(DATA!R12C6:R54C6,CDPS!RC[-4],DATA!R12C8:R54C8)"
    .Offset(, 5).Value = "=SUMIF(DATA!R12C7:R54C7,CDPS!RC[-5],DATA!R12C9:R54C9)"
    .Offset(, 6).Value = "=MAX(RC[-4]+RC[-2]-RC[-3]-RC[-1],0)"
    .Offset(, 7).Value = "=MAX(RC[-4]+RC[-2]-RC[-5]-RC[-3],0)"
    With .Offset(, 4).Resize(, 4): .Value = .Value: End With
  End With
End Sub

Có ngắn hơn bao nhiêu đâu bác, cách viết code đưa nhiều lệnh trên cùng một dòng sau dấu (:) để nhìn cho có vẻ ngắn không nên dùng, với những hàm có khối lượng code nhiều nó làm cho người ta khó đọc. Viết code cho người mới học nên thật dễ hiểu, dùng comment để giải thích.
 
Upvote 0
To yome:
Xin góp ý bạn thế này:
1/ Sh data không cần thiết cột I và H luôn = I
2/ SHTK trên CĐPS cũng không nên có vừa TK cấp 1 và cấp 2 => khó kiểm tra tổng số phát sinh.
3/ Và số dư đầu kỳ cũng không cần thiết tạo thêm ở ở sh DMTK, sao kg đưa vào CĐPS luôn, đàng nào cũng copy từ tháng trước.
Vài lời xin đóng góp.
 
Upvote 0
Có ngắn hơn bao nhiêu đâu bác, cách viết code đưa nhiều lệnh trên cùng một dòng sau dấu (:) để nhìn cho có vẻ ngắn không nên dùng, với những hàm có khối lượng code nhiều nó làm cho người ta khó đọc. Viết code cho người mới học nên thật dễ hiểu, dùng comment để giải thích.
Tuân không để ý rồi
Ngăn gọn nằm ở chổ này:
PHP:
Sub taoso2()
  With Range([A12], [A65536].End(xlUp))
    .Offset(, 4).Value = .....
    .Offset(, 5).Value = .....
    .Offset(, 6).Value = .....
    .Offset(, 7).Value = .....
  End With
End Sub
Khỏi phải copy/paste (chứ không phải nằm ở mấy chổ dấu 2 chấm) ---> Đồng ý chứ?
 
Upvote 0
Các anh/chị ơi, em làm được rồi! Nhưng em vẫn muốn hỏi thêm 1 chút nữa để hoàn thiện tốt hơn. Cụ thể là:
- Nếu tài khoản cấp 1 (gồm 3 số) thì nó vẫn chưa cộng tổng được (vì có một số tài khoản phải chi tiết các tài khoản nhỏ mà!?)
Em muốn thực hiện theo công thức này sao lại không được:
PHP:
 Range("E12").Formula = "=SUMIF(DATA!$F$12:$F$54,CDPS!A12,DATA!$H$12:$H$54)"
Thành:

PHP:
Range("E12").Formula = "=SUMIF(DATA!$F$12:$F$54,CDPS!A12&"*",DATA!$H$12:$H$54)"

Vậy em muốn hỏi đoạn code thứ 2 sửa như thế nào cho đúng!?
Cám ơn các anh/chị nhiều!
 
Lần chỉnh sửa cuối:
Upvote 0
Sửa dùm em code cập nhật dữ liệu nhé!

Em có ví dụ này, không hiểu sao code em lỗi ở chỗ nào mà các đánh màu vàng (file đính kèm) không cho kết quả đúng!? Em đã kiểm tra mà vẫn chưa phát hiện chỗ sai. Anh chị chỉ dùm em chỗ sai nhé!
File đính kèm
 
Upvote 0
Các anh/chị ơi, em làm được rồi! Nhưng em vẫn muốn hỏi thêm 1 chút nữa để hoàn thiện tốt hơn. Cụ thể là:
- Nếu tài khoản cấp 1 (gồm 3 số) thì nó vẫn chưa cộng tổng được (vì có một số tài khoản phải chi tiết các tài khoản nhỏ mà!?)
Em muốn thực hiện theo công thức này sao lại không được:
PHP:
 Range("E12").Formula = "=SUMIF(DATA!$F$12:$F$54,CDPS!A12,DATA!$H$12:$H$54)"
Thành:

PHP:
Range("E12").Formula = "=SUMIF(DATA!$F$12:$F$54,CDPS!A12&"*",DATA!$H$12:$H$54)"

Vậy em muốn hỏi đoạn code thứ 2 sửa như thế nào cho đúng!?
Cám ơn các anh/chị nhiều!
Nhìn sơ qua đã biết viết kiểu đó sẽ sai
Bạn cứ gõ công thức trên bảng tính, đồng thời record macro sẽ biết công thức ấy đựoc code thể hiện thế nào thôi, có khó khăn gì đâu?
Công thức cũ sẽ đựoc code biểu diển thành:
"=SUMIF(DATA!R12C6:R54C6,CDPS!RC[-4],DATA!R12C8:R54C8)"
Còn cái mới thì vầy:
"=SUMIF(DATA!R12C6:R54C6,CDPS!RC[-4]&""*"",DATA!R12C8:R54C8)"
----------------
Lưu ý:
Bạn phải dùng công thức kiểu R1C1 thì mới có thể gán cùng lúc công thức cho nguyên cột đựoc
 
Upvote 0
Nó chẳng sai gì cả chỉ sai kiểu dữ liệu thôi. Bảng DMTK dạng Number còn trong bảng CDPS dạng Text nên nó không tìm ra mà thôi. Bạn thêm dấu nháy " ' " vào trước số 911 xem. Nó lại OK

Thay câu lệnh:

With .Offset(, 1).Resize(, 8): .Value = .Value: End With

Bằng:

.Resize(, 8).Value = .Resize(, 8).Value
 
Lần chỉnh sửa cuối:
Upvote 0
Nó chẳng sai gì cả chỉ sai kiểu dữ liệu thôi. Bảng DMTK dạng Number còn trong bảng CDPS dạng Text nên nó không tìm ra mà thôi. Bạn thêm dấu nháy " ' " vào trước số 911 xem. Nó lại OK

Thay câu lệnh:

With .Offset(, 1).Resize(, 8): .Value = .Value: End With

Bằng:

.Resize(, 8).Value = .Resize(, 8).Value

Cám ơn anh sealand nhiều!
 
Upvote 0
Nhìn sơ qua đã biết viết kiểu đó sẽ sai
Bạn cứ gõ công thức trên bảng tính, đồng thời record macro sẽ biết công thức ấy đựoc code thể hiện thế nào thôi, có khó khăn gì đâu?
Công thức cũ sẽ đựoc code biểu diển thành:
"=SUMIF(DATA!R12C6:R54C6,CDPS!RC[-4],DATA!R12C8:R54C8)"
Còn cái mới thì vầy:
"=SUMIF(DATA!R12C6:R54C6,CDPS!RC[-4]&""*"",DATA!R12C8:R54C8)"
----------------
Lưu ý:
Bạn phải dùng công thức kiểu R1C1 thì mới có thể gán cùng lúc công thức cho nguyên cột đựoc

Cám ơn Thầy ndu nhiều! Em đã làm lại được rồi bằng cách record marco.
Vì là mới học, em rất mong thầy chỉ giúp!
 
Upvote 0
Tuân không để ý rồi
Ngăn gọn nằm ở chổ này:
PHP:
Sub taoso2()
With Range([A12], [A65536].End(xlUp))
.Offset(, 4).Value = .....
.Offset(, 5).Value = .....
.Offset(, 6).Value = .....
.Offset(, 7).Value = .....
End With
End Sub
Khỏi phải copy/paste (chứ không phải nằm ở mấy chổ dấu 2 chấm) ---> Đồng ý chứ?

Thầy ndu ơi! Em muốn có dòng tổng cộng dưới cùng của bảng tính. Em đã sửa code thế này:
Mã:
Sub taoso()
  With Range([A12], [A65536].End(xlUp))
    .Offset(, 1).Value = "=VLOOKUP(A12,DMTK20,2,0)"
    .Offset(, 2).Value = "=VLOOKUP(A12,DMTK20,4,0)"
    .Offset(, 3).Value = "=VLOOKUP(A12,DMTK20,5,0)"
    .Offset(, 4).Value = "=SUMIF(DATA!$F$12:$F$65000,CDPS!A12&""*"",DATA!$H$12:$H$65000)"
    .Offset(, 5).Value = "=SUMIF(DATA!$G$12:$G$65000,CDPS!A12&""*"",DATA!$I$12:$I$65000)"
    .Offset(, 6).Value = "=MAX(C12+E12-D12-F12,0)"
    .Offset(, 7).Value = "=MAX(D12+F12-C12-E12,0)"
    [B][COLOR=red].Range("C75").Formula= "=Subtotal(9,$C$12:$C$74)[/COLOR][/B]
[B][COLOR=red]    .Range("D75").Formula= "=Subtotal(9,$D$12:$D$74)[/COLOR][/B]
    '...Các dòng dưới nữa
    .Resize(, 8).Value = .Resize(, 8).Value
    'With .Offset(, 1).Resize(, 8): .Value = .Value: End With
Dim eRw As Long, jJ As Byte:          Dim WF, Cls As Range
  eRw = [B65500].End(xlUp).Row
 Application.ScreenUpdating = False
 Set WF = Application.WorksheetFunction
 For Each Cls In [C12].Resize(eRw)
   If WF.CountIf(Cls.Resize(, 4), "<>0") = 0 Then
      Cls.EntireRow.Hidden = True
   End If
   Next Cls
End With
End Sub
Sao nó không cho kết quả đúng!?
 
Upvote 0
Thầy ndu sửa dùm em chút:

Mã:
Sub taoso()
  With Range([A12], [A65536].End(xlUp))
    .Offset(, 1).Value = "=VLOOKUP(A12,DMTK20,2,0)"
    .Offset(, 2).Value = "=VLOOKUP(A12,DMTK20,4,0)"
    .Offset(, 3).Value = "=VLOOKUP(A12,DMTK20,5,0)"
    .Offset(, 4).Value = "=SUMIF(DATA!$F$12:$F$65000,CDPS!A12&""*"",DATA!$H$12:$H$65000)"
    .Offset(, 5).Value = "=SUMIF(DATA!$G$12:$G$65000,CDPS!A12&""*"",DATA!$I$12:$I$65000)"
    .Offset(, 6).Value = "=MAX(C12+E12-D12-F12,0)"
    .Offset(, 7).Value = "=MAX(D12+F12-C12-E12,0)"
    [B][COLOR=red].Range("C75").Formula= "=Subtotal(9,$C$12:$C$74)[/COLOR][/B]
[B][COLOR=red]    .Range("D75").Formula= "=Subtotal(9,$D$12:$D$74)[/COLOR][/B]
    '...Các dòng dưới nữa
    .Resize(, 8).Value = .Resize(, 8).Value
    'With .Offset(, 1).Resize(, 8): .Value = .Value: End With
Dim eRw As Long, jJ As Byte:          Dim WF, Cls As Range
  eRw = [B65500].End(xlUp).Row
 Application.ScreenUpdating = False
 Set WF = Application.WorksheetFunction
 For Each Cls In [C12].Resize(eRw)
   If WF.CountIf(Cls.Resize(, 4), "<>0") = 0 Then
      Cls.EntireRow.Hidden = True
   End If
   Next Cls
End With
End Sub

Cám ơn thầy nhiều!
 
Upvote 0
Thầy ndu sửa dùm em chút:

Mã:
Sub taoso()
  With Range([A12], [A65536].End(xlUp))
    .Offset(, 1).Value = "=VLOOKUP(A12,DMTK20,2,0)"
    .Offset(, 2).Value = "=VLOOKUP(A12,DMTK20,4,0)"
    .Offset(, 3).Value = "=VLOOKUP(A12,DMTK20,5,0)"
    .Offset(, 4).Value = "=SUMIF(DATA!$F$12:$F$65000,CDPS!A12&""*"",DATA!$H$12:$H$65000)"
    .Offset(, 5).Value = "=SUMIF(DATA!$G$12:$G$65000,CDPS!A12&""*"",DATA!$I$12:$I$65000)"
    .Offset(, 6).Value = "=MAX(C12+E12-D12-F12,0)"
    .Offset(, 7).Value = "=MAX(D12+F12-C12-E12,0)"
    [B][COLOR=red].Range("C75").Formula= "=Subtotal(9,$C$12:$C$74)[/COLOR][/B]
[B][COLOR=red]    .Range("D75").Formula= "=Subtotal(9,$D$12:$D$74)[/COLOR][/B]
    '...Các dòng dưới nữa
    .Resize(, 8).Value = .Resize(, 8).Value
    'With .Offset(, 1).Resize(, 8): .Value = .Value: End With
Dim eRw As Long, jJ As Byte:          Dim WF, Cls As Range
  eRw = [B65500].End(xlUp).Row
 Application.ScreenUpdating = False
 Set WF = Application.WorksheetFunction
 For Each Cls In [C12].Resize(eRw)
   If WF.CountIf(Cls.Resize(, 4), "<>0") = 0 Then
      Cls.EntireRow.Hidden = True
   End If
   Next Cls
End With
End Sub
Cám ơn thầy nhiều!
Phải vầy chứ
PHP:
.Offset(.Rows.Count, 3)(1, 1).Value = "=Subtotal(109," & .Offset(, 3).Address & ")"
.Offset(.Rows.Count, 4)(1, 1).Value = "=Subtotal(109," & .Offset(, 4).Address & ")"
.Offset(.Rows.Count, 5)(1, 1).Value = "=Subtotal(109," & .Offset(, 5).Address & ")"
.Offset(.Rows.Count, 6)(1, 1).Value = "=Subtotal(109," & .Offset(, 6).Address & ")"
.Offset(.Rows.Count, 7)(1, 1).Value = "=Subtotal(109," & .Offset(, 7).Address & ")"
---------------------
Lưu ý quan trọng:
- Cái vụ ẩn dòng có ảnh hưởng rất lớn đến End(xlUp)
- Nếu trước khi chạy code mà không cho hiện lại dòng ẩn thì có thể sai bị sai (khá nguy hiểm)
 
Lần chỉnh sửa cuối:
Upvote 0
Dùng Advanced Filter

Thay vì dùng For.. Next, tôi cải tiến dùng Advanced Filter đây
PHP:
Sub Taoso()
  On Error Resume Next
  With Range([A11], [A65536].End(xlUp))
    .Parent.ShowAllData
    .Offset(1, 1).Value = "=VLOOKUP(A12,DMTK20,2,0)"
    .Offset(1, 2).Value = "=VLOOKUP(A12,DMTK20,4,0)"
    .Offset(1, 3).Value = "=VLOOKUP(A12,DMTK20,5,0)"
    .Offset(1, 4).Value = "=SUMIF(DATA!$F$12:$F$65000,CDPS!A12&""*"",DATA!$H$12:$H$65000)"
    .Offset(1, 5).Value = "=SUMIF(DATA!$G$12:$G$65000,CDPS!A12&""*"",DATA!$I$12:$I$65000)"
    .Offset(1, 6).Value = "=MAX(C12+E12-D12-F12,0)"
    .Offset(1, 7).Value = "=MAX(D12+F12-C12-E12,0)"
    .Offset(.Rows.Count, 1).ClearContents
    .Offset(.Rows.Count, 2)(1, 1).Value = "=Subtotal(109," & .Offset(, 2).Address & ")"
    .Offset(.Rows.Count, 3)(1, 1).Value = "=Subtotal(109," & .Offset(, 3).Address & ")"
    .Offset(.Rows.Count, 4)(1, 1).Value = "=Subtotal(109," & .Offset(, 4).Address & ")"
    .Offset(.Rows.Count, 5)(1, 1).Value = "=Subtotal(109," & .Offset(, 5).Address & ")"
    .Offset(.Rows.Count, 6)(1, 1).Value = "=Subtotal(109," & .Offset(, 6).Address & ")"
    .Offset(.Rows.Count, 7)(1, 1).Value = "=Subtotal(109," & .Offset(, 7).Address & ")"
    With .Resize(, 8)
      .Value = .Value
      .AdvancedFilter 1, [L1:L2]
    End With
  End With
End Sub
Chú ý cell L2 có công thức điều kiện lọc nhé!
 

File đính kèm

Upvote 0
Thầy ndu sửa dùm em chút:

Mã:
Sub taoso()
  With Range([A12], [A65536].End(xlUp))
    .Offset(, 1).Value = "=VLOOKUP(A12,DMTK20,2,0)"
    .Offset(, 2).Value = "=VLOOKUP(A12,DMTK20,4,0)"
    .Offset(, 3).Value = "=VLOOKUP(A12,DMTK20,5,0)"
    .Offset(, 4).Value = "=SUMIF(DATA!$F$12:$F$65000,CDPS!A12&""*"",DATA!$H$12:$H$65000)"
    .Offset(, 5).Value = "=SUMIF(DATA!$G$12:$G$65000,CDPS!A12&""*"",DATA!$I$12:$I$65000)"
    .Offset(, 6).Value = "=MAX(C12+E12-D12-F12,0)"
    .Offset(, 7).Value = "=MAX(D12+F12-C12-E12,0)"
    [B][COLOR=red].Range("C75").Formula= "=Subtotal(9,$C$12:$C$74)[/COLOR][/B]
[B][COLOR=red]    .Range("D75").Formula= "=Subtotal(9,$D$12:$D$74)[/COLOR][/B]
    '...Các dòng dưới nữa
    .Resize(, 8).Value = .Resize(, 8).Value
    'With .Offset(, 1).Resize(, 8): .Value = .Value: End With
Dim eRw As Long, jJ As Byte:          Dim WF, Cls As Range
  eRw = [B65500].End(xlUp).Row
 Application.ScreenUpdating = False
 Set WF = Application.WorksheetFunction
 For Each Cls In [C12].Resize(eRw)
   If WF.CountIf(Cls.Resize(, 4), "<>0") = 0 Then
      Cls.EntireRow.Hidden = True
   End If
   Next Cls
End With
End Sub
Cám ơn thầy nhiều!


1/ $C$12:$C$75 ====> Sao khong record macro xem VBA nó viết ra sao? Dấu $ chỉ sử dụng trên bảng tính còn trong VBA viết thế nó không hiểu là gì hết. Yome để ý $C$12 chính là hàng 12 cột 3(cộtC) đúng không? Vậy thì trong VBA phải viết là R12C3 nó mới hiểu
2/ Nguyên cả đoạn chữ đỏ của yome có thể thay như sau

n=Range("A65000").End(xlUp).Row
Range("C" & n+1 & ":H" & n+1)="=SUM(R12C:R" & n & "C)"

Thân
 
Lần chỉnh sửa cuối:
Upvote 0
Anh xem ở bảng CDPS thấy có nhiều chỗ sai lắm đó nha!!không biết có phải vì số liệu chỉ giả lập thôi không nữa ??? Nếu là dữ liệu thật thì em phải xem lại định khoản tại sheet DATa.
Theo nhu bản chat của bảng CDPS thì tong số dư Nợ= tổng số dư Có.
Kiểm tra sơ bộ anh thấy các tài khoản chi phí có số dư cuối kỳ???.
 
Upvote 0
1/ $C$12:$C$75 ====> Sao khong record macro xem VBA nó viết ra sao? Dấu $ chỉ sử dụng trên bảng tính còn trong VBA viết thế nó không hiểu là gì hết. Yome để ý $C$12 chính là hàng 12 cột 3(cộtC) đúng không? Vậy thì trong VBA phải viết là R12C3 nó mới hiểu
Ấy! Nó hiểu tất đấy! Không tin cứ thí nghiệm sẽ biết liền
 
Upvote 0
Mình không có file cụ thể nên chưa tìm lý do chứ code nhập công thức sau không có gì sai cả

Mã:
   .Range("C75").Formula= "=Subtotal(9,$C$12:$C$74)
    .Range("D75").Formula= "=Subtotal(9,$D$12:$D$74)
 
Upvote 0
Mình không có file cụ thể nên chưa tìm lý do chứ code nhập công thức sau không có gì sai cả

Mã:
   .Range("C75").Formula= "=Subtotal(9,$C$12:$C$74)
    .Range("D75").Formula= "=Subtotal(9,$D$12:$D$74)
Sai ở chổ .Range(..) ấy anh à! (ở trên là With Range(....) )
Không có dấu chấm thì được!
Có điều ở đây đang xét 1 Dynamic Range ---> Không thể ghi chính xác Range nào được ---> Dữ liệu dài bao nhiêu dòng thì công thức tổng phải tự động chạy theo đến đấy!
(File cụ thể nằm tại bài #1 đấy)
 
Upvote 0
Thay vì dùng For.. Next, tôi cải tiến dùng Advanced Filter đây
PHP:
Sub Taoso()
  On Error Resume Next
  With Range([A11], [A65536].End(xlUp))
    .Parent.ShowAllData
    .Offset(1, 1).Value = "=VLOOKUP(A12,DMTK20,2,0)"
    .Offset(1, 2).Value = "=VLOOKUP(A12,DMTK20,4,0)"
    .Offset(1, 3).Value = "=VLOOKUP(A12,DMTK20,5,0)"
    .Offset(1, 4).Value = "=SUMIF(DATA!$F$12:$F$65000,CDPS!A12&""*"",DATA!$H$12:$H$65000)"
    .Offset(1, 5).Value = "=SUMIF(DATA!$G$12:$G$65000,CDPS!A12&""*"",DATA!$I$12:$I$65000)"
    .Offset(1, 6).Value = "=MAX(C12+E12-D12-F12,0)"
    .Offset(1, 7).Value = "=MAX(D12+F12-C12-E12,0)"
    .Offset(.Rows.Count, 1).ClearContents
    .Offset(.Rows.Count, 2)(1, 1).Value = "=Subtotal(109," & .Offset(, 2).Address & ")"
    .Offset(.Rows.Count, 3)(1, 1).Value = "=Subtotal(109," & .Offset(, 3).Address & ")"
    .Offset(.Rows.Count, 4)(1, 1).Value = "=Subtotal(109," & .Offset(, 4).Address & ")"
    .Offset(.Rows.Count, 5)(1, 1).Value = "=Subtotal(109," & .Offset(, 5).Address & ")"
    .Offset(.Rows.Count, 6)(1, 1).Value = "=Subtotal(109," & .Offset(, 6).Address & ")"
    .Offset(.Rows.Count, 7)(1, 1).Value = "=Subtotal(109," & .Offset(, 7).Address & ")"
    With .Resize(, 8)
      .Value = .Value
      .AdvancedFilter 1, [L1:L2]
    End With
  End With
End Sub
Chú ý cell L2 có công thức điều kiện lọc nhé!
Chào anh ndu và các thành viên
Tôi thấy File đính kèm chỉ có Sheet "DMTK", sao tôi thấy code ở dưới là "DMTK20" cho hỏi số 20 này là gì ???
PHP:
    .Offset(1, 1).Value = "=VLOOKUP(A12,DMTK20,2,0)"
    .Offset(1, 2).Value = "=VLOOKUP(A12,DMTK20,4,0)"
    .Offset(1, 3).Value = "=VLOOKUP(A12,DMTK20,5,0)"
Cảm ơn!
 
Upvote 0
Chào anh ndu và các thành viên
Tôi thấy File đính kèm chỉ có Sheet "DMTK", sao tôi thấy code ở dưới là "DMTK20" cho hỏi số 20 này là gì ???
PHP:
    .Offset(1, 1).Value = "=VLOOKUP(A12,DMTK20,2,0)"
    .Offset(1, 2).Value = "=VLOOKUP(A12,DMTK20,4,0)"
    .Offset(1, 3).Value = "=VLOOKUP(A12,DMTK20,5,0)"
Cảm ơn!
Bác ơi, đó là Name được tạo trong bảng tính đó, Bác bấm Ctrl+F3 để xem các name tạo trong đó nhé !
DMTK20=DMTK!$A$6:$E$78

Các thao tác trong CODE trên phần lớn là gán công thức cho cells và chuyển về Value thôi ạ!
 
Lần chỉnh sửa cuối:
Upvote 0
Bác ơi, đó là Name được tạo trong bảng tính đó, Bác bấm Ctrl+F3 để xem các name tạo trong đó nhé !
DMTK20=DMTK!$A$6:$E$78

Các thao tác trong CODE trên phần lớn là gán công thức cho cells và chuyển về Value thôi ạ!
Bình thường, cũng hay kiểm tra Name. Hôm nay lại quên phéng vụ này
Cảm ơn đã nhắc nhở!
 
Upvote 0

Bài viết mới nhất

Back
Top Bottom