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

[phankhang]

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

 

[phongkiemtinh]

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.

 

[SA_DQ]

[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:

 '. . . . . . . . . ' 
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

 

[phongkiemtinh]

[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:

 '. . . . . . . . . ' 
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.

 

[SA_DQ]

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):

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;

 

[phongkiemtinh]

(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.

 

[ChanhTQ@]

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.)

 

[phongkiemtinh]

Cảm ơn các bác đã support nha. có gì nhờ các bác chỉ bảo thêm.

 

[phongkiemtinh]

(9)

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.

 

[nghiaphuc]

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.