Chuyển công thức excel thành code VBA (1 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

 

[ndu96081631]

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ì

 

[le tin]

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

 

[ThuNghi]

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:

Sub DoTim()
With Cells(1, 5)
  .FormulaR1C1 = "=VLOOKUP(RC1,[Source.xls]Mua!R1C1:R7C2,2,0)"
  .Value = .Value
End With
End Sub

 

[le tin]

Em ghi lại Macro và tạo code như sau:

Sub DoTim()
With Cells(1, 5)
  .FormulaR1C1 = "=VLOOKUP(RC1,[Source.xls]Mua!R1C1:R7C2,2,0)"
  .Value = .Value
End With
End Sub

Nếu công thức thì mình đã làm rồi đó

 

[MinhNguyệt]

Nếu công thức thì mình đã làm rồi đó

Vậy thì ý bạn là sao?

 

[le tin]

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

 

[ndu96081631]

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

 

[le tin]

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

 

[ndu96081631]

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)

 

[le tin]

Bạn giúp mình với Ví dụ này
Cảm ơn nhiều

 

[ndu96081631]

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:

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é

 

[le tin]

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

 

[andredat]

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.

 

[phongkiemtinh]

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

 

[Quang_Hả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

Đọ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

 

[SA_DQ]

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

. . . . . . .

 

[phongkiemtinh]

Đọ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.

 

[SA_DQ]

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

 

[phongkiemtinh]

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

 

[SA_DQ]

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

 [AB3].CurrentRegion.Offset(4).Copy Destination:=Sh.[A2]

Xin bạn sửa giúp thành:

 [AB3].CurrentRegion.Offset(3).Copy Destination:=Sh.[A2]   '4*'

Mong được trao đổi tiếp với bạn.