Chuyển công thức excel thành code VBA (1 người xem)

Liên hệ QC

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

phankhang

Thành viên mới
Tham gia
31/7/07
Bài viết
18
Được thích
1
Chào các anh em trong GPE . Tôi là người mới tầm sư học đạo excel và chưa biết gì về VBA . Diễn đàn này đã giúp tôi rất nhiều trong quá trình tiếp cận . Hiện nay tôi có 1 vấn đề nan giải , rất mong các anh chị giúp cho.

Hiện nay tôi đang lập bảng lương bằng excel cho công nhân nhưng tương đối phức tạp . Dòng công thức như sau
=IF(OR(CCONG!F6="O",CCONG!F6="CO",CCONG!F6="P",CCONG!F6="ts",CCONG!F6="NO",CCONG!F6="U"),0,
IF(OR(LEFT($B6,2)="pl",LEFT($B6,2)="gv",LEFT($B6,2)="NL"),VLOOKUP("K1",XNL,DAY(F$5)+2,0)*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0),
IF(OR(LEFT($B6,2)="CB",LEFT($B6,2)="GT",LEFT($B6,2)="lv"),VLOOKUP($B6,SO_KG,DAY($F$5)+2,0)*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0),VLOOKUP("KDH",NTP,DAY(F$5)+2,0)*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0))))*IF($B6<>"BBDH",1,VLOOKUP($C6,GT_BB,4,0))

Với công thức này khi copy ra hàng ngàn cell thì file excel rất nặng và khi click vào cell thì thanh formula kéo dài xuống tận 1/2 màn hình . Có cách nào chuyển công thức trên thành VBA được không

Tôi rất lơ mơ nên nếu có gì không đúng , mong các bạn chỉ giáo
 
Đọc công thức của bạn mình hoa hết cả mắt, chẳng hiểu gì

Nếu bạn muốn chuyển đổi sang VBA thì phải nêu rõ điều kiện và yêu cầu của mình ra. Công thức của bạn tính toán cái gì, thông tin cần lấy là gì?

Để chuyển công thức từ Excel sang VBA có hai cách:
- activesheet.range("a1").formular1c1="=sum(.....)": đặt công thức sum tại ô A1
- application.worksheetfuntion.sum(.....): tạo hàm tính tổng trong VBA
 
Upvote 0
Rất cám ơn bạn đã trả lời . Mình giải thích thêm như sau :
Mục đích của công thức trên là tính lương cho từng người theo từng ngày ( theo từng bộ phận khác nhau ), mỗi bộ phận có định mức lương khác nhau . Giải thích công thức trên như sau :

1. Nếu bảng chấm công là O , co , ... thì lương ngày đó là 0 đồng
2.Nếu bộ phận làm việc là "pl", "gv",... thì tính lương theo nguyên liệu xuất vào sản xuất , tra theo bảng nguyên liệu xuất * tra theo đơn giá nguyên liệu * số giờ làm việc của cá nhân / tổng số giờ làm việc của cả bộ phận
3. Nếu bộ phận làm việc là "CB", "BB",... thì tính lương theo thành phẩm sản xuất ra , tra theo bảng thành phẩm nhập kho* tra theo đơn giá thành phẩm* số giờ làm việc của cá nhân / tổng số giờ làm việc của cả bộ phận

4. Nếu bộ phận làm việc là "BBDH" thì nhân với hệ số ( tra ở bảng riêng ) còn nếu ở bộ phận khác thì hệ số 1

Thật ra đây chỉ là 1 thông số thôi chứ thực tế còn nhiều bảng khác nữa do mỗi ngày làm nhiều loại nguyên liệu khác nhau với đơn giá khác nhau , nhiều loại thành phẩm khác nhau với đơn giá khác nhau . Công nhân có thể trong 1 ngày làm ở 2,3 bộ phận khác nhau với những đơn giá hoàn toàn khác nhau ( do sản xuất rau quả nên không chủ động được nguyên liệu . Khi nguyên liệu này hết thì phải chuyển sang làm nguyên liệu khác mà mỗi loại nguyên liệu thì số người ở mỗi tổ thay đổi do khối lượng công việc khác nhau )

Không biết giải thích như vậy đã rõ chưa . Cám ơn bạn nhiều

Mình gửi kèm file rút gọn lên . Rất mong chỉ giáo
 

File đính kèm

Chỉnh sửa lần cuối bởi điều hành viên:
Upvote 0
Đúng là hoa cả mắt,

anhthuan đã viết:
Để chuyển công thức từ Excel sang VBA có hai cách:
- activesheet.range("a1").formular1c1="=sum(.....)": đặt công thức sum tại ô A1
- application.worksheetfuntion.sum(.....): tạo hàm tính tổng trong VBA

- cách này thì đặt nguyên c/thức giống như đặt tay ->cũng hay

- nhưng cách hay là lập hàm VBA (UDF)để thay công thức lun

- vấn đề? bạn nên upload file lên có vd và giải thích công thức thì mọi ng mới giúp được
 
Upvote 0
vì vội, tigertiger mới chỉ

Dùng cách đặt tên động cho bạn -> c thức ngắn hơn và dễ kiểm soát hơn, và phát hiện thành phần này -> bạn nên đặt thừa số chung ra ngoài
[FONT=&quot]*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0)[/FONT]
nhưng chắc còn nh tồn tại

tigertiger quay lại chủ đề sớm
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
Bạn tham khảo thêm hàm tự tạo sau:

Mã:
Option Explicit

[B]Function TienCong(CgOm As Range, MaTo As Range, Ngay As Range)[/B]
'[COLOR="Blue"]=IF(OR(CCONG!F6="O";CCONG!F6="CO";CCONG!F6="P";CCONG!F6="ts";CCONG!F6="NO";CCONG!F6="U");0; _
IF(OR(LEFT($B6;2)="pl";LEFT($B6;2)="gv";LEFT($B6;2)="NL");VLOOKUP("K1";XNL;DAY(F$5)+2;0) _
    *VLOOKUP($B6;DGIA;3;0)*CCONG!F6/VLOOKUP($B6;TG_GL;DAY($F$5)+2;0); _
IF(OR(LEFT($B6;2)="CB";LEFT($B6;2)="GT";LEFT($B6;2)="lv");VLOOKUP($B6;SO_KG;DAY($F$5)+2;0) _
    *VLOOKUP($B6;DGIA;3;0)*CCONG!F6/VLOOKUP($B6;TG_GL;DAY($F$5)+2;0); _
    VLOOKUP("KDH";NTP;DAY(F$5)+2;0)*VLOOKUP($B6;DGIA;3;0)*CCONG!F6/VLOOKUP($B6;TG_GL;DAY($F$5)+2;0)))) _
    *IF($B6<>"BBDH";1; VLOOKUP($C6;GT_BB;4;0)) [/COLOR] 
 Dim lMTo As String
 Dim DonGia As Double, TGGL As Double, Dat As Long
 
 CgOm = UCase$(CgOm):           lMTo = UCase$(Left(MaTo, 2))
 Dat = Day(Ngay) + 2
 
 DonGia = Application.VLookup(MaTo, DGIA, 3, 0)
 TGGL = Application.VLookup(MaTo, TG_GL, Dat, 0)
 
 If CgOm = "O" Or CgOm = "CO" Or CgOm = "P" Or CgOm = "TS" Or CgOm = "NO" Or CgOm = "U" Then
    TienCong = 0: Exit Function
 ElseIf lMTo = "PL" Or lMTo = "GV" Or lMTo = "NL" Then
    TienCong = Application.VLookup("K1", XNL, Dat, 0) * DonGia * CgOm / TGGL [COLOR="red"]' <= K1?[/COLOR]
 ElseIf lMTo = "CB" Or lMTo = "GT" Or lMTo = "LV" Then
    TienCong = Application.VLookup(MaTo, SO_KG, Dat, 0) * DonGia * CgOm / TGGL
 Else
    TienCong = Application.VLookup("KDH", NTP, Dat, 0) * DonGia * CgOm / TGGL
 End If
 If MaTo = "BBDH" Then TienCong = TienCong * Application.VLookup(C6, GT_BB, 4, 0) [COLOR="Red"]'<= C6?![/COLOR] 
[B]End Function [/B]

Hàm này chưa hoàn toàn kết thúc; chỉ để tham khảo thôi!
 
Upvote 0
Có có cách khác là dùng VBA. Khi chèn công thức xong là chuyển qua giá trị luôn việc này mình nghĩ cũng đơn giản bởi lương thì không có nhiều dòng.
 
Upvote 0
Tôi xin có một chút ý kiến như vâỳ
để nắm bát nhanh cho những ngươì mơí tập viết VB trong excell, nêú có vấn đè gì chưa hiêủ bạn cứ record những thao tác cuả bạn trên bảng tính kể cả lập công thức, sau đó stop record rôì vaò code để xem và rút kinh nghiệm, hôì đó lúc chưa rành VB tôi cũng hay làm cách naỳ, khá hữu hiêụ đâý các bạn ạ.
 
Upvote 0
rockfusion đã viết:
Tôi xin có một chút ý kiến như vâỳ
để nắm bát nhanh cho những ngươì mơí tập viết VB trong excell, nêú có vấn đè gì chưa hiêủ bạn cứ record những thao tác cuả bạn trên bảng tính kể cả lập công thức, sau đó stop record rôì vaò code để xem và rút kinh nghiệm, hôì đó lúc chưa rành VB tôi cũng hay làm cách naỳ, khá hữu hiêụ đâý các bạn ạ.

Cách này chỉ ứng dụng trong những việc nhỏ thui. còn những việc lớn như trích xuất dữ liệu, lấy dữ liệu từ nhiều sheet thành 1 sheet ... thì không biết VBA cũng thua thui.
Mặc khác những đoạn code do ghi lại từ record sẽ không chạy trơn tru & nhanh bằng thêm mắm muối gạo tiền vào, đặc biệt các đoạn bẫy lỗi trong code.....


Vài dòng gửi bạn.

Thân chào.
 
Upvote 0
phankhang đã viết:
Chào các anh em trong GPE . Tôi là người mới tầm sư học đạo excel và chưa biết gì về VBA . Diễn đàn này đã giúp tôi rất nhiều trong quá trình tiếp cận . Hiện nay tôi có 1 vấn đề nan giải , rất mong các anh chị giúp cho.

Hiện nay tôi đang lập bảng lương bằng excel cho công nhân nhưng tương đối phức tạp . Dòng công thức như sau
=IF(OR(CCONG!F6="O",CCONG!F6="CO",CCONG!F6="P",CCONG!F6="ts",CCONG!F6="NO",CCONG!F6="U"),0,
IF(OR(LEFT($B6,2)="pl",LEFT($B6,2)="gv",LEFT($B6,2)="NL"),VLOOKUP("K1",XNL,DAY(F$5)+2,0)*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0),
IF(OR(LEFT($B6,2)="CB",LEFT($B6,2)="GT",LEFT($B6,2)="lv"),VLOOKUP($B6,SO_KG,DAY($F$5)+2,0)*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0),VLOOKUP("KDH",NTP,DAY(F$5)+2,0)*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0))))*IF($B6<>"BBDH",1,VLOOKUP($C6,GT_BB,4,0))
Tôi vận dụng code của Bác SA hoàn chỉnh thêm cho bạn nhưng hưa test và không bẫy lỗi. Bạn cứ dùng sai đến đâu sửa đến đó.
Mã:
Option Explicit
Dim lMTo As String
Dim DonGia As Double, TGGL As Double, iDate As Long
Dim DGIA As Range, TG_GL As Range, XNL As Range, SO_KG As Range, NTP As Range, GT_BB As Range
Public Function TienCong(MaNV As Range, MaTo As Range, Ngay As Date, CgOm As Range)
Set DGIA = Range("DGIA")
Set TG_GL = Range("TG_GL")
Set XNL = Range("XNL")
Set SO_KG = Range("SO_KG")
Set NTP = Range("NTP")
Set GT_BB = Range("GT_BB")
' CgOm = UCase$(CgOm)
 lMTo = UCase$(Left(MaTo, 2))
 iDate = Day(Ngay) + 2

DonGia = Application.VLookup(MaTo, DGIA, 3, 0)
TGGL = Application.VLookup(MaTo, TG_GL, iDate, 0)
 
If Not IsNumeric(CgOm) Then
    TienCong = 0: Exit Function
ElseIf lMTo = "PL" Or lMTo = "GV" Or lMTo = "NL" Then
    TienCong = Application.VLookup("K1", XNL, iDate, 0) * DonGia * CgOm / TGGL ' <= K1?
ElseIf lMTo = "CB" Or lMTo = "GT" Or lMTo = "LV" Then
    TienCong = Application.VLookup(MaTo, SO_KG, iDate, 0) * DonGia * CgOm / TGGL
Else
    TienCong = Application.VLookup("KDH", NTP, iDate, 0) * DonGia * CgOm / TGGL
End If
If MaTo = "BBDH" Then TienCong = TienCong * Application.VLookup(MaNV, GT_BB, 4, 0) '<= C6?!
Set DGIA = Nothing
Set TG_GL = Nothing
Set XNL = Nothing
Set SO_KG = Nothing
Set NTP = Nothing
Set GT_BB = Nothing

End Function
Tại sh DHBC, từ F6 bạn nhập ct: Tiencong(MaNV,MaTo,Ngay,CgOm) cụ thể là
F6=tiencong($C6,$B6,F$5,CCONG!F6)
Bạn tổ chức nhiều bảng quá, lương gì phức tạp thế.
 

File đính kèm

Chỉnh sửa lần cuối bởi điều hành viên:
Upvote 0
Xin cám ơn tất cả các bạn . Số là tôi không phải là dân chuyên về tin học mà rặt ròng là dân hóa thực phẩm . Tuy nhiên do hệ thống bảng lương công nhân của mình quá phức tạp . Đại loại như sau :

- Mỗi ngày sản xuất nhiều sản phẩm khác nhau với đơn giá khác nhau
- Mỗi loại nguyên liệu cũng có đơn giá khác nhau
- Mỗi công nhân một ngày có thể tham gia nhiều loại sản phẩm , nhiều loại nguyên liệu
- Đơn giá từng công đoạn cũng khác nhau , có công đoạn ăn lương riêng từng nhóm người , có công đoạn ăn lương cá nhân , có loại lương theo thời gian . Mỗi người 1 ngày có khả năng làm 1 vài giờ ở chế độ lương này và 1 vài giờ khác ở chế độ lương khác
- Nhóm ăn lương tập thể cũng không có số người cố định mà có thể thay đổi tùy theo mặt hàng nào , nguyên liệu nào ,...
- Và còn một số rắc rối khác nữa

Tôi thường để ý thấy bộ phận làm lương công nhân thường làm bằng excel và gần như không ứng dụng được gì các công thức cả mà mỗi ô lương phải cộng bằng tay ( đại loại sử dụng excel giống như thay máy caculator). Tôi có nhã ý muốn giúp các em làm lương đỡ vất vả hơn và quan trọng là không được làm sai , số liệu phải báo cáo hàng ngày để lãnh đạo có thể ra quyết định điều chỉnh kịp thời . Nhưng rõ ràng là khi bắt tay vô mới thấy cái khó vì với số vốn ít ỏi tự học của mình việc hoàn thành đồ án này còn quá xa vời .

Thú thật tôi cũng đã nhờ một vài công ty phần mềm đến khảo sát nhưng hình như họ cũng "ngán " hay sao đó mà lặn mất tiêu . Thôi thì đành vừa làm vừa học vậy . Biết đến đâu làm đến đó . Các y kiến của các bạn đều giúp tôi rất nhiều trong việc dần dần tiếp cận

Nhân đây nếu các bạn nào thấy có thể chấp nhận thử thách với những yêu cầu trên , mình sẵn sàng hợp tác kể cả với tư cách cá nhân ( không phải pháp nhân cũng được) để hoàn thành đồ án trên . Chi phí thì thỏa thuận . Vui lòng liên hệ theo private messages . Cám ơn tất cả các bạn
 
Upvote 0
Nhân đây nếu các bạn nào thấy có thể chấp nhận thử thách với những yêu cầu trên , mình sẵn sàng hợp tác kể cả với tư cách cá nhân ( không phải pháp nhân cũng được) để hoàn thành đồ án trên . Chi phí thì thỏa thuận . Vui lòng liên hệ theo private messages . Cám ơn tất cả các bạn
LVDuyệt (Và Bình) xem xét & đứng ra nhận lời cũng như tổ chức thực hiện cho GPE;
Chắc sẽ có những người góp phần . . . cho GPE! Mình tin như vậy!
 
Upvote 0
Nếu các bạn nhận làm mình sẽ gửi yêu cầu chi tiết
 
Upvote 0
To Thunghi :

Cám ơn thunghi , cho mình hỏi thêm : Mình thấy bạn sử dụng công thức tại ô F6 có đoạn
=CCONG!F6>100

để thay thế cho cái đoạn
=OR(CCONG!F6="O",CCONG!F6="CO",CCONG!F6="P",CCONG!F6="ts",CCONG!F6="NO",CCONG!F6="U")

của mình kết quả rất đúng mà hay . Bạn có thể vui lòng giải thích hộ xem tại sao bạn lại sử dụng như vậy không . Mình chưa hiểu lắm , đúng là các bạn đã dạy cho mình rất nhiều
 
Upvote 0
phankhang đã viết:
To Thunghi :
Cám ơn thunghi , cho mình hỏi thêm : Mình thấy bạn sử dụng công thức tại ô F6 có đoạn
=CCONG!F6>100
để thay thế cho cái đoạn
=OR(CCONG!F6="O",CCONG!F6="CO",CCONG!F6="P",CCONG!F6="ts",CCONG!F6="NO",CCONG!F6="U")
Nếu F6 là text, chữ luôn luôn lớn hơn số, có thể thay =CCONG!F6>1000000000
Bạn thử cho đại F6="c", dùng thử =F6>1000000000 nó sẽ báo là true.
ie nếu F6 là "O", "CO"... thì luôn > số nào đó.
Nôm na dễ hiểu.
 
Upvote 0
SA_DQ đã viết:
LVDuyệt (Và Bình) xem xét & đứng ra nhận lời cũng như tổ chức thực hiện cho GPE;
Chắc sẽ có những người góp phần . . . cho GPE! Mình tin như vậy!
Mong Duyệt và Bình chủ trì nhận thực hiện, mình tin sẽ có nhiều anh em khác tham gia, như hôm trước có trao đổi với Bình đó
 
Upvote 0
Em xin cám ơn anh SA_DQ và anh Kenvil.

To: Phankhang,
Bạn có thể liên hệ với tôi theo e-mail: levanduyet@yahoo.com
Nhưng cho tôi hỏi, nếu trong trường hợp các ý tưởng chúng tôi thực hiện cho bạn nếu đưa lên GPE, thì bạn có đồng ý không?

Lê Văn Duyệt
 
Upvote 0
Đường dẫn trong VBA

Chào các bạn
Nhờ các bạn giúp mình đường dẫn ... để ghi code trong trường hợp này

Application.WorksheetFunction.VLookup([c1],, 2, 0)
CÔNG THỨC:
=VLOOKUP(C1;'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8;2;0)

Cảm ơn nhiều
 
Upvote 0
Chào các bạn
Nhờ các bạn giúp mình đường dẫn ... để ghi code trong trường hợp này

Application.WorksheetFunction.VLookup([c1],, 2, 0)
CÔNG THỨC:
=VLOOKUP(C1;'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8;2;0)

Cảm ơn nhiều
Anh dùng thử code sau:
PHP:
Sub DoTim()
Dim WF As WorksheetFunction
Set WF = WorksheetFunction
Dim myRng As Range
Set myRng = Sheet1.Range(Cells(1, 1), Cells(4, 2))
If WF.CountIf(myRng.Resize(, 1), Cells(1, 4)) > 0 Then
  Cells(1, 5) = WF.VLookup(Cells(1, 4), myRng, 2, 0)
  MsgBox Cells(1, 5)
Else
  MsgBox "Khong tim thay"
End If
End Sub
Trong đó MyRng là 'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8
Có 2 cách, dùng .Formular cũng được.
 
Upvote 0
Cảm ơn ThuNghi , ý trên là trong công thức vùng chọn là 'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8 thì trong VBA phải ghi vào chỗ dấu (... ) như thế nào .
Trong đó MyRng là 'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8 cụ thể ghi thế nào vậy bạn
 
Upvote 0
Cảm ơn ThuNghi , ý trên là trong công thức vùng chọn là 'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8 thì trong VBA phải ghi vào chỗ dấu (... ) như thế nào .
Trong đó MyRng là 'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8 cụ thể ghi thế nào vậy bạn
Cái này quá đơn giản ---> Bạn thử record macro quá trình bạn nhập công thức (bằng tay) rồi xem code nó viết gì là biết liền chứ gì
 
Upvote 0
Cái này quá đơn giản ---> Bạn thử record macro quá trình bạn nhập công thức (bằng tay) rồi xem code nó viết gì là biết liền chứ gì
Bạn xem giúp mình VD ở #19 , dữ liệu ở 1 file khác , dùng macro ghi lại công thức bằng tay nhưng muốn đưa vào code thì không biết sửa thế nào .
Cảm ơn
 
Upvote 0
Bạn xem giúp mình VD ở #19 , dữ liệu ở 1 file khác , dùng macro ghi lại công thức bằng tay nhưng muốn đưa vào code thì không biết sửa thế nào .
Cảm ơn
Em ghi lại Macro và tạo code như sau:
PHP:
Sub DoTim()
With Cells(1, 5)
  .FormulaR1C1 = "=VLOOKUP(RC1,[Source.xls]Mua!R1C1:R7C2,2,0)"
  .Value = .Value
End With
End Sub
 
Upvote 0
Vậy thì ý bạn là sao?
Mình diễn đạt lại thế này
Tại điạ chỉ A1 gõ công thức
=VLOOKUP(C1;'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8;2;0)
Nay muốn viết code cho ô này
[A1]=Application.WorksheetFunction.VLookup([c1], , 2, 0)

Bạn thay giúp chỗ 3 chấm màu đỏ để hoàn thiện code
 
Upvote 0
Mình diễn đạt lại thế này
Tại điạ chỉ A1 gõ công thức
=VLOOKUP(C1;'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8;2;0)
Nay muốn viết code cho ô này
[A1]=Application.WorksheetFunction.VLookup([c1], , 2, 0)

Bạn thay giúp chỗ 3 chấm màu đỏ để hoàn thiện code
Thì ThuNghi đã làm cho bạn y vậy ở trên rồi còn gì
Có nhiều cách để đạt được mục đích, đâu nhất thiết phải Application.WorksheetFunction chứ
Công thức dài, cách đơn giản là viết "nguyên con" vào code, sau đó chuyển nó thành Value ---> Đơn giản thế thôi, không hiểu sao bạn lại cứ hỏi lòng vòng hoài
 
Upvote 0
Thì ThuNghi đã làm cho bạn y vậy ở trên rồi còn gì
Có nhiều cách để đạt được mục đích, đâu nhất thiết phải Application.WorksheetFunction chứ
Công thức dài, cách đơn giản là viết "nguyên con" vào code, sau đó chuyển nó thành Value ---> Đơn giản thế thôi, không hiểu sao bạn lại cứ hỏi lòng vòng hoài
Cảm ơn bạn NDU , đây là một ví dụ đơn giản để lấy dữ liệu từ 1 file đang đóng .
Ý đồ là mở rộng ra như muốn lọc thì áp dụng như thế nào
 
Upvote 0
Cảm ơn bạn NDU , đây là một ví dụ đơn giản để lấy dữ liệu từ 1 file đang đóng .
Ý đồ là mở rộng ra như muốn lọc thì áp dụng như thế nào
Bạn gữi cả 2 file lên đây (1 file nguồn và 1 file muốn lấy dử liệu)... Hy vọng sẽ giúp bạn trực tiếp vào file luôn (mất công hướng dẩn bạn lại không hiểu)
 
Upvote 0
Bạn giúp mình với Ví dụ này
Cảm ơn nhiều
 

File đính kèm

Upvote 0
Bạn giúp mình với Ví dụ này
Cảm ơn nhiều
Sao lạ vậy ta... Lúc trước bạn hỏi khác, bây giờ đưa file lên lại hỏi 1 vấn đề không liên quan gì đến VLOOKUP cả
Hic...
Dù sao cũng cứ làm:
PHP:
Sub Loc()
  Application.ScreenUpdating = False
  Range("A:A").Clear
  Workbooks.Open ThisWorkbook.Path & "\Data.xls"
  ThisWorkbook.Activate
  With Workbooks("Data.xls").Sheets("Sheet1").Range("A1:A12")
    .AdvancedFilter 2, , Range("A1"), True
  End With
  Workbooks("Data.xls").Close (False)
  Application.ScreenUpdating = True
End Sub
Code này nằm trong file CT.xls nhé
 
Upvote 0
Cảm ơn NDU , sở dĩ không đưa Vlookup vì sợ bạn mất công như ThuNghi , mình lay hoay mãi mà không nghĩ đến cách phải mở file .
Bạn giúp thêm 1 tí nữa , nếu file Data đang mở thì thực hiện , không cảnh báo và không đóng file
Cảm ơn nhiều lắm
 
Lần chỉnh sửa cuối:
Upvote 0
Chào các bác,
Mình cũng đang tập viết code vba đây. Mình đồng ý là dùng record macro lại để học là rất tốt nhưng với một sheet có nhiều dòng nhiều cột và sử dụng những công thức phức tạp thì Excel chạy rất chậm. Mình muốn hỏi có cách nào mình chuyển các công thức trong Excel thành các code tương đương vì khi đó trong ô chứa kết quả không còn công thức tính toán nữa thì lúc đó Excel thật tuyệt. Ví dụ đơn giản
Vùng C1:c10 =A1:A10 + B1:b10
thì lúc này khi ta nhập liệu vào vùng B1:b10 thì ta có giá trị ở cột C tương ứng .
Mong các bác giúp đỡ. Cảm ơn.
 
Upvote 0
Mình có file này nhờ mọi người giúp đỡ với.

Mình có file làm xử lý bằng tay hằng ngày, yêu cầu các bước như ở sheet "Note", màu vàng.
Mình muốn nhờ các bác GPEX giúp làm macro thực hiện các bước trên dùm.Vì số cell xử lý lớn lặp lại nên công thức kéo lâu.
Thêm nữa là dữ liệu đầu vào cố định số cột như vậy, nhưng số dòng dữ liệu lúc ít, lúc nhiều.
Cảm ơn các bác
 
Upvote 0
Mình có file làm xử lý bằng tay hằng ngày, yêu cầu các bước như ở sheet "Note", màu vàng.
Mình muốn nhờ các bác GPEX giúp làm macro thực hiện các bước trên dùm.Vì số cell xử lý lớn lặp lại nên công thức kéo lâu.
Thêm nữa là dữ liệu đầu vào cố định số cột như vậy, nhưng số dòng dữ liệu lúc ít, lúc nhiều.
Cảm ơn các bác
Đọc chẳng hiểu gì cả. Nhất là thấy tô màu mè càng thêm khó cho việc động não. Hic
 
Upvote 0
[ThongBao]Mình có file làm xử lý bằng tay hằng ngày, yêu cầu các bước như ở sheet "Note";

Mình muốn nhờ các bác làm giúp macro thực hiện các bước trên dùm. Vì số cell xử lý lớn lặp lại nên công thức kéo lâu.
Thêm nữa là dữ liệu đầu vào cố định số cột như vậy, nhưng số dòng dữ liệu lúc ít, lúc nhiều.
Cảm ơn các bác[/ThongBao]

Bạn xem thử trước đi nha;

Nhưng theo mình ta nên lọc trước khi xếp theo trật tự (Cái này cũng nhờ bạn thử đi vậy!)

Đọc chẳng hiểu gì . . .Hic

=IF(ISERROR(VLOOKUP(C2,Note!$A$42:$A$500,1,0))=FALSE,"LIQUID 48h", IF(OR(LEFT(E2,3)="DOW",LEFT(E2,3)="FEB",LEFT(E2,3)="AMB",LEFT(E2,3)="FBR"),"LIQUID",
IF(OR(LEFT(E2,3)="DYN",LEFT(E2,3)="FAB",LEFT(E2,3)="TRO",LEFT(E2,3)="TID",LEFT(E2,3)="VN ",LEFT(E2,3)="TH ",LEFT(E2,3)="BNX"),"LAUNDRY",
IF(OR(LEFT(E2,3)="H&S",LEFT(E2,3)="PTN",LEFT(E2,3)="RJC",LEFT(E2,3)="REJ",LEFT(E2,3)="PAN",LEFT(E2,3)="HS "),"HAIRCARE"))))

(Màu đỏ): Nếu không tìm thấy trị tại [C2] trong bảng tra (ở trang 'Note') thì trả về "LIQUID 48H"

(Màu Xanh): Nếu 3 chữ cái đầu của [E2] có trong chuỗi "DOW_FEB_AMB_FBR" thì trả về "LIQUID"

. . . . . . .
 
Chỉnh sửa lần cuối bởi điều hành viên:
Upvote 0
Đọc chẳng hiểu gì cả. Nhất là thấy tô màu mè càng thêm khó cho việc động não. Hic
yêu cầu ở sheet "note" từ cell A2 đến A14 đó bác.
Thanks

[ThongBao]Mình có file làm xử lý bằng tay hằng ngày, yêu cầu các bước như ở sheet "Note";

Mình muốn nhờ các bác làm giúp macro thực hiện các bước trên dùm. Vì số cell xử lý lớn lặp lại nên công thức kéo lâu.
Thêm nữa là dữ liệu đầu vào cố định số cột như vậy, nhưng số dòng dữ liệu lúc ít, lúc nhiều.
Cảm ơn các bác[/ThongBao]

Bạn xem thử trước đi nha;

Nhưng theo mình ta nên lọc trước khi xếp theo trật tự (Cái này cũng nhờ bạn thử đi vậy!)

Sao khi chạy báo lỗi bác ah.Lỗi ở chỗ này, bác tạo them sheet nên khó hiểu chút
Rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("AJ1:AK2"), CopyToRange:=Range("AA3:AK3"), Unique:=False
Bác xem lại dùm chút nha, thanks bác nhiều

(Màu đỏ): Nếu không tìm thấy trị tại [C2] trong bảng tra (ở trang 'Note') thì trả về "LIQUID 48H"

(Màu Xanh): Nếu 3 chữ cái đầu của [E2] có trong chuỗi "DOW_FEB_AMB_FBR" thì trả về "LIQUID"

. . . . . . .
Đây là công thức dò tìm giá trị so sánh với cái bảng ở sheet note đó bạn, nếu nằm trong danh sách thì là mẫu "LIQUID 48H"
Mình đang thắc mắc khi ghi macro theo các bước trên thì :
ví dụ dữ liệu hôm nay 100 dòng thì macro ghi lại các bước chỉ thực hiện xử lý được 100 dòng, còn nếu dữ liệu nhiều hơn 100 dòng thì macro không xử lý phần hơn đó.
Mình muốn macro có thể xử lý bao nhiêu dòng không theo dữ liệu thay đổi.
 
Chỉnh sửa lần cuối bởi điều hành viên:
Upvote 0
(1) Bác tạo them sheet nên khó hiểu chút
(2) Sao khi chạy báo lỗi bác ah.Lỗi ở chỗ này:
Rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("AJ1:AK2"), CopyToRange:=Range("AA3:AK3"), Unique:=False
Bác xem lại dùm chút nha, thanks bác nhiều

(1) Có 2 trang lưu số liệu để đối chiếu thôi;

(2) Đúng mà mình chưa nói bạn chổ này: Muốn chạy mới, ta cần chép trang lưu sang 'Source'
Vì iêu cầu ban đầu là xóa các dòng không có dữ liệu (Cụ thể là mình đã cho macro xóa đi chỉ 2 dòng cần thiết mà thôi.)
Bỡi vậy nên trang 'Source' sau khi chạy không còn như thiết chế ban đầu.

Còn giờ mình đã thêm vô cuối macro các dòng lệnh đề trả về thiết chế ban đầu rồi;
Bạn kiểm tra số liệu xem sao; Mình thấy số liệu lệch với bạn; & do vậy mới hỏi thêm công việc của hàm như bài trên(?)

Mình đang thắc mắc khi ghi macro theo các bước trên thì :
ví dụ dữ liệu hôm nay 100 dòng thì macro ghi lại các bước chỉ thực hiện xử lý được 100 dòng, còn nếu dữ liệu nhiều hơn 100 dòng thì macro không xử lý phần hơn đó.
Mình muốn macro có thể xử lý bao nhiêu dòng không theo dữ liệu thay đổi.

Vấn đề này sẽ vượt qua, nếu vô đầu mỗi lần chạy, bạn tìm cách xác định dòng cuối có dữ liệu của CSDL

Cụ thể hơn:

Bạn hãy chọn 1 trường (cột) nào nhất thiết fải có dữ liệu (thật ra, theo mình thì trường nào cũng cần có dữ liệu hết trơn đó!)
Vô đầu macro bạn xác định dòng cuối có dữ liệu của trường này bằng fương thức
Dim Rws As Long
Rws= Cells(65500,"X").End(xlUp).Row

( X là trường đã nói bên trên)



(Mình sẽ tháo file bên trên, để đỡ tốn tài nguyên)
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
(1) Có 2 trang lưu số liệu để đối chiếu thôi;

(2) Đúng mà mình chưa nói bạn chổ này: Muốn chạy mới, ta cần chép trang lưu sang 'Source'
Vì iêu cầu ban đầu là xóa các dòng không có dữ liệu (Cụ thể là mình đã cho macro xóa đi chỉ 2 dòng cần thiết mà thôi.)
Bỡi vậy nên trang 'Source' sau khi chạy không còn như thiết chế ban đầu.

Còn giờ mình đã thêm vô cuối macro các dòng lệnh đề trả về thiết chế ban đầu rồi;
Bạn kiểm tra số liệu xem sao; Mình thấy số liệu lệch với bạn; & do vậy mới hỏi thêm công việc của hàm như bài trên(?)



Vấn đề này sẽ vượt qua, nếu vô đầu mỗi lần chạy, bạn tìm cách xác định dòng cuối có dữ liệu của CSDL

Cụ thể hơn:

Bạn hãy chọn 1 trường (cột) nào nhất thiết fải có dữ liệu (thật ra, theo mình thì trường nào cũng cần có dữ liệu hết trơn đó!)
Vô đầu macro bạn xác định dòng cuối có dữ liệu của trường này bằng fương thức
Dim Rws As Long
Rws= Cells(65500,"X").End(xlUp).Row

( X là trường đã nói bên trên)



(Mình sẽ tháo file bên trên, để đỡ tốn tài nguyên)

Bác làm mà kết quả ra không đúng rùi, chạy loan cả. Bác xem thử kết quả e ra ở sheet "result", còn source e rút gọn lai ít data hơn để bác so sánh xem chạy đúng không. Kết quả là e làm tay.
 
Lần chỉnh sửa cuối:
Upvote 0
kết quả ra không đúng rùi, chạy loan cả. Bác xem thử kết quả e ra ở sheet "result", còn source e rút gọn lai ít data hơn để bác so sánh xem chạy đúng không. Kết quả là e làm tay.

Mình đã kiểm kết quả tại 'Result' và thấy như thế này:

Công thức tại cột 'L' bạn tham chiếu đi đâu đó chứ không tham chiếu theo bảng mà mình đã gán tên 'GName' ở trang 'Note';

Nếu đối chiếu giữa trị tại cột 'C' của trang này với bảng 'GName' ta sẽ có kết quả như sau:

Material|Dòng tại trang 'Note'
82172479|86
82174178|Nothing
82176624|Nothing
82184430|Nothing
82186853|Nothing
82187991|Nothing

Như vậy, theo mình hiểu thì mình đã làm đúng 5 records

Còn 1 record mình chép thiếu, đây là lỗi của mình trong câu lệnh
Mã:
 [AB3].CurrentRegion.Offset(4).Copy Destination:=Sh.[A2]
Xin bạn sửa giúp thành:
PHP:
 [AB3].CurrentRegion.Offset(3).Copy Destination:=Sh.[A2]   '4*'

Mong được trao đổi tiếp với bạn.
 
Upvote 0
Mình đã kiểm kết quả tại 'Result' và thấy như thế này:

Công thức tại cột 'L' bạn tham chiếu đi đâu đó chứ không tham chiếu theo bảng mà mình đã gán tên 'GName' ở trang 'Note';

Nếu đối chiếu giữa trị tại cột 'C' của trang này với bảng 'GName' ta sẽ có kết quả như sau:

Material|Dòng tại trang 'Note'
82172479|86
82174178|Nothing
82176624|Nothing
82184430|Nothing
82186853|Nothing
82187991|Nothing

Như vậy, theo mình hiểu thì mình đã làm đúng 5 records
Mong được trao đổi tiếp với bạn.

Tham chiếu vào cột C, nếu đối chiếu có mặt cột C ở 'Gname' thì trả về Liquid 48h, (82172479 = liquid 48h) nhưng mình thấy kết quả bạn trả về là 'liqiud'.Bạn xem dùm tham chiếu trong code có bị sai không.

Còn khi đối chiếu không cột C không có trong 'gname' thì bắt đầu dò theo 3 kí tự đầu tiên ở cột E để trã về tương ứng.
 
Upvote 0
[Thongbao]Tham chiếu vào cột C, nếu đối chiếu có mặt cột C ở 'Gname' thì trả về Liquid 48h, (82172479 = liquid 48h) nhưng mình thấy kết quả bạn trả về là 'liqiud'.Bạn xem dùm tham chiếu trong code có bị sai không.

Còn khi đối chiếu không cột C không có trong 'gname' thì bắt đầu dò theo 3 kí tự đầu tiên ở cột E để trã về tương ứng.[/ThongBao]

Đúng là mình hiểu nhằm do dịch sai ý của công thức :

=IF(ISERROR(VLOOKUP(C2,Note!$A$42:$A$500,1,0))=FALSE,"LIQUID 48h",. . .)

Vậy bạn sửa giúp trong đoạn mã như sau:

Mã:
 '. . . . . . . . . ' 
For Each Cls In Range([C2], [C2].End(xlDown))
    Set sRng = Rng.Find(Cls.Value, , xlFormulas, xlWhole)
    If Not sRng Is Nothing Then                             '*[B][COLOR="#FF0000"]Not[/COLOR][/B]*'    
        Cells(Cls.Row, "L").Value = "LIQUID 48h"
    Else
        If InStr(LIQUID, Left(Cls.Offset(, 2), 3)) Then
            Cells(Cls.Row, "L").Value = "LIQUID"
        ElseIf InStr(LAUNDRY, Left(Cls.Offset(, 2), 3)) Then
            Cells(Cls.Row, "L").Value = "LAUNDRY"
        ElseIf InStr(HAIRCARE, Left(Cls.Offset(, 2), 3)) Then
            Cells(Cls.Row, "L").Value = "HAIRCARE"
        End If
    End If
 Next Cls
 Worksheets("Source").Select
 Range("2:2,4:4").Select
 Selection.Insert Shift:=xlDown
 Sh.Select:                             Set Sh = Nothing
End Sub
 
Upvote 0
[Thongbao]Tham chiếu vào cột C, nếu đối chiếu có mặt cột C ở 'Gname' thì trả về Liquid 48h, (82172479 = liquid 48h) nhưng mình thấy kết quả bạn trả về là 'liqiud'.Bạn xem dùm tham chiếu trong code có bị sai không.

Còn khi đối chiếu không cột C không có trong 'gname' thì bắt đầu dò theo 3 kí tự đầu tiên ở cột E để trã về tương ứng.[/Thongbao]

Đúng là mình hiểu nhằm do dịch sai ý của công thức :

=IF(ISERROR(VLOOKUP(C2,Note!$A$42:$A$500,1,0))=FALSE,"LIQUID 48h",. . .)

Vậy bạn sửa giúp trong đoạn mã như sau:

Mã:
 '. . . . . . . . . ' 
For Each Cls In Range([C2], [C2].End(xlDown))
    Set sRng = Rng.Find(Cls.Value, , xlFormulas, xlWhole)
    If Not sRng Is Nothing Then                             '*[B][COLOR=#FF0000]Not[/COLOR][/B]*'    
        Cells(Cls.Row, "L").Value = "LIQUID 48h"
    Else
        If InStr(LIQUID, Left(Cls.Offset(, 2), 3)) Then
            Cells(Cls.Row, "L").Value = "LIQUID"
        ElseIf InStr(LAUNDRY, Left(Cls.Offset(, 2), 3)) Then
            Cells(Cls.Row, "L").Value = "LAUNDRY"
        ElseIf InStr(HAIRCARE, Left(Cls.Offset(, 2), 3)) Then
            Cells(Cls.Row, "L").Value = "HAIRCARE"
        End If
    End If
 Next Cls
 Worksheets("Source").Select
 Range("2:2,4:4").Select
 Selection.Insert Shift:=xlDown
 Sh.Select:                             Set Sh = Nothing
End Sub
Giờ thì đúng rồi cảm ơn bạn nhiều nha, ah bạn giúp mình chuyển code luôn công thức bước cuối

'9.Tai cot M nhap cong thuc =F2+G2+IF(L2="LIQUID","28:00:00",IF(L2="LAUNDRY","12:00:00","52:00:00"))

code thế này đúng không bạn(mình làm bào lỗi):

Sheets("Leadtime").Select
ActiveCell.FormulaR1C1 = _
"=RC[-7]+RC[-6]+IF(RC[-1]=""LIQUID"",""28:00:00"",IF(RC[-1]=""LAUNDRY"",""12:00:00"",""52:00:00""))"
Selection.AutoFill Destination:=Range("M2:M" & Rws), Type:=xlFillDefault


'10. Dinh dang lai cot M thanh dd.mm.yyyy hh.mm
11. lọc theo "Haircare" ra 1 sheet mới, "Liquid", "liquid 48h " ra 1 sheet luôn được không.
 
Lần chỉnh sửa cuối:
Upvote 0
Giờ bạn giúp mình chuyển code luôn công thức bước cuối

'9.Tai cot M nhap cong thuc =F2+G2+IF(L2="LIQUID","28:00:00",IF(L2="LAUNDRY","12:00:00","52:00:00"))

code thế này đúng không bạn(mình làm bào lỗi):
PHP:
Sheets("Leadtime").Select 
ActiveCell.FormulaR1C1 = _
"=RC[-7]+RC[-6]+IF(RC[-1]=""LIQUID"",""28:00:00"",IF(RC[-1]=""LAUNDRY"",""12:00:00"",""52:00:00""))"
Selection.AutoFill Destination:=Range("M2:M" & Rws), Type:=xlFillDefault

'10. Dinh dang lai cot M thanh dd.mm.yyyy hh.mm
11. lọc theo "Haircare" ra 1 sheet mới, "Liquid", "liquid 48h " ra 1 sheet luôn được không.

(9) Thực ra bước này cần trãi qua 2 công đoạn (Bạn xem thêm trong macro);

(10) Mình đã định dạng cho bạn, nhưng mình thầy bước này e vậy là hơi sớm

(11) Ta nên cắt ra làm 2 macro;
Macro1 chỉ nên làm đến bước 9 mà thôi;
Hai bước còn lại ta sang trang 'Report' để làm tiếp thì hay hơn

& mình đã thực hiện gần với cách 2 này, có nghĩa là bạn đến ô [M2] của trang này mà lựa tùy thích theo iêu cầu;
 

File đính kèm

Upvote 0
(9) Thực ra bước này cần trãi qua 2 công đoạn (Bạn xem thêm trong macro);

(10) Mình đã định dạng cho bạn, nhưng mình thầy bước này e vậy là hơi sớm

(11) Ta nên cắt ra làm 2 macro;
Macro1 chỉ nên làm đến bước 9 mà thôi;
Hai bước còn lại ta sang trang 'Report' để làm tiếp thì hay hơn

& mình đã thực hiện gần với cách 2 này, có nghĩa là bạn đến ô [M2] của trang này mà lựa tùy thích theo iêu cầu;
Sao khi chay với source ít hơn hoặc nhiều hơn thì báo lỗi bạn ah:

Rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("AJ1:AK2"), CopyToRange:=Range("AA3:AK3"), Unique:=False
bạn xem lai dùm mình với.
 
Upvote 0
Nhiều sẽ chậm chư không thể báo lỗi

Sao khi chay với source ít hơn hoặc nhiều hơn thì báo lỗi bạn ah:

Rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("AJ1:AK2"), CopyToRange:=Range("AA3:AK3"), Unique:=False
bạn xem lai dùm mình với.

Một khi bạn giữ nguyện thiết chế 5 dòng đầu như bài #1 của bạn, hay trong file (Cho đến cột 'AL' của trang 'Souce' nha! )
Dữ liệu của bạn bắt đầu từ dòng 6

Nếu dòng 6 trở đi mà có dòng trống thì macro sẽ không chịu làm việc đâu đó.

(Chú í rằng bạn không thể chép dữ liệu đâu đó đem đến 'Souce' & chép đè lên toàn bộ trang này; Như vậy vừa lười biếng, vừa gây khó cho macro.)
 
Upvote 0
Cảm ơn các bác đã support nha. có gì nhờ các bác chỉ bảo thêm.
 
Upvote 0
Bác HYén7 ơi giúp mình chút với, e muốn khóa dữ liệu theo vùng từ A1:E5 thôi. những cell ngoài vùng trên sau khi Save vẫn không khóa. Bác xem file đính kèm và thêm code vao file dùm e với, xem dùm code e viết cần thêm gì nữa.
Bạn nên tiếp tục vấn đề tại đây nhé. Hỏi ở topic này là chen ngang đấy.

Nhờ Mod xóa 2 bài này giùm.
 
Upvote 0

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

Back
Top Bottom