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

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
yome

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

Sắp xếp theo thời gian sắp xếp theo bầu chọn
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
tongquangtuyen đã viết:
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 đó.
Nhấp chuột vào đây để mở rộng...

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
Nguyễn Duy Tuân đã viết:
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
Nhấp chuột vào đây để mở rộng...

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

yome đã viết:
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é!
Nhấp chuột vào đây để mở rộng...

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
yome đã viết:
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)
Nhấp chuột vào đây để mở rộng...

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
ndu96081631 đã viết:
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
Nhấp chuột vào đây để mở rộng...
Nếu có thể NDU làm 1 code = Dic thử có nhanh hơn.
Cám ơn NDU nhiều.
 
Upvote 0
ThuNghi đã viết:
Nếu có thể NDU làm 1 code = Dic thử có nhanh hơn.
Cám ơn NDU nhiều.
Nhấp chuột vào đây để mở rộng...
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
ndu96081631 đã viết:
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
Nhấp chuột vào đây để mở rộng...

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
Nguyễn Duy Tuân đã viết:
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.
Nhấp chuột vào đây để mở rộng...
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
yome đã viết:
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ấp chuột vào đây để mở rộng...
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
sealand đã viết:
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
Nhấp chuột vào đây để mở rộng...

Cám ơn anh sealand nhiều!
 
Upvote 0
ndu96081631 đã viết:
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
Nhấp chuột vào đây để mở rộng...

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
ndu96081631 đã viết:
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ứ?
Nhấp chuột vào đây để mở rộng...

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
yome đã viết:
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!
Nhấp chuột vào đây để mở rộng...
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
yome đã viết:
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!
Nhấp chuột vào đây để mở rộng...


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
XuanThanh đã viết:
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
Nhấp chuột vào đây để mở rộng...
Ấ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
sealand đã viết:
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)
Nhấp chuột vào đây để mở rộng...
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
ndu96081631 đã viết:
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é!
Nhấp chuột vào đây để mở rộng...
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
dongducnhiem đã viết:
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!
Nhấp chuột vào đây để mở rộng...
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
hoamattroicoi đã viết:
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 ạ!
Nhấp chuột vào đây để mở rộng...
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ạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.

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

H
Đánh số thứ thự phiếu thu chi
Trả lời
4
Đọc
70
SA_DQ
SA_DQ
Gà Công Nghệ
Tô màu họ và tên bị trùng từ 2 sheet khác nhau
Trả lời
1
Đọc
42
HUONGHCKT
H
B
Dùng button trên forrm để gọi bàn phím ảo win 10(OSK)
Trả lời
4
Đọc
72
Bảo_Anh_
B
P
VẼ ĐƯỜNG TẦN SUẤT (DTS) TRÊN EXCEL (1 người xem)
Trả lời
8
Đọc
164
ptm0412
ptm0412
Nguyenkhang2404
giúp đỡ lọc dữ liệu nâng cao
Trả lời
21
Đọc
627
thanthanhan
thanthanhan
P
GOAL SEEK NÂNG CAO TRONG EXCEL
    • Thích
Trả lời
4
Đọc
167
Hoale85
H
B
Gán Phím Tắt Gọi Form (1 người xem)
Trả lời
7
Đọc
76
HeSanbi
HeSanbi
K
Điền giá trị ô thay vì địa chỉ ô
Trả lời
2
Đọc
116
LOGANWover
L
G
Chia sẻ các bạn File vẽ kèo Zamil từ excel
    • Thích
    • Yêu thích
Trả lời
11
Đọc
888
trqt82
T
T
Xin trợ giúp cách lọc trùng về khoảng thời gian
Trả lời
10
Đọc
2K
Phan Thế Hiệp
Phan Thế Hiệp
L
Xin hàm trích xuất chuỗi trong ô
Trả lời
12
Đọc
171
LINH7979
L
H
[Xin giúp đỡ] Tìm thời gian trùng ở nhiều khoảng thời gian khác nhau.
Trả lời
14
Đọc
2K
Phan Thế Hiệp
Phan Thế Hiệp
N
Thuế thu nhập cá nhân
    • Chấp nhận
Trả lời
3
Đọc
251
ngouyen1505
N
D
Lọc các ô có dữ liệu trùng nhau trong một cột. Xin chỉ giúp
Trả lời
7
Đọc
283
Gà Công Nghệ
Gà Công Nghệ
Gà Công Nghệ
Tự động điều chỉnh căn giãn dữ liệu theo độ rộng của ô trong excel
Trả lời
4
Đọc
123
Gà Công Nghệ
Gà Công Nghệ
bitun1991
Xin nhờ hỗ trợ làm file excel giải đấu bóng đá (1 người xem)
Trả lời
1
Đọc
69
Duy Tùng 9x
Duy Tùng 9x
tuanlemanh
Chữ trong ô bị nhảy dòng khi in
Trả lời
2
Đọc
59
tuanlemanh
tuanlemanh
tramcannt
các bác giúp em với ạ
Trả lời
0
Đọc
53
tramcannt
tramcannt
S
Tạo sheet mới từ dữ liệu 2 sheet khác ĐVT (1 người xem)
Trả lời
0
Đọc
51
skmart
S
X
Làm thế nào để ẩn "Blank" trong Pivot table
    • Thích
Trả lời
14
Đọc
36K
hoangpham1105
H
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Thời gian đếm ngược.

000
Ngày
00
Giờ
00
phút
00
giây
Thân mời tham dự sự kiện sinh nhật GPE 2025
Back
Top Bottom