Công thức excel trong câu lệnh VBA

Liên hệ QC
H

hic1802

Thành viên tiêu biểu
Tham gia
16/2/13
Bài viết
545
Được thích
34
Giới tính
Nam
Chào mọi người trên GPE,
Em có vấn đề muốn nhờ mọi người giúp đỡ trong việc cải tiến tốc độ xử lý code VBA câu lệnh trên Excel
Em có viết câu lệnh trên VBA các lệnh excel, sau đó chỉ lấy giá trị của nó (bỏ công thức đi để file excel nhẹ, dễ mở khi dữ liệu điền vào lớn)
Tuy nhiên e chạy VBA thấy load chậm quá, nhờ mọi người giúp có cách gì khác để xử lý bài toán này không?
VBA này e dùng record macro + chế thêm

Mã: 
Sub rundata()
On Error Resume Next
Dim i As Long, lr As Long
'Sheets("BDL").Range("A6:BH9999").Borders.LineStyle = 0
lr = Sheets("BDL").Cells(Sheets("BDL").Rows.Count, "A").End(xlUp).Row
If lr <= 4 Then
    MsgBox "Chua co du lieu"
    Exit Sub
End If
With Sheets("BDL")
For i = 5 To lr
    If .Range("BH" & i).Value = "" Then
        .Range("X" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-23]&""_""&DAY(RC[-10])&MONTH(RC[-10]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrDau'!R3C1:R999999C5,5,0),"""")"
        .Range("X" & i) = .Range("X" & i).Value
        .Range("y" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-24]&""_""&DAY(RC[-11])&MONTH(RC[-11]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrGiua'!R3C1:R999999C5,5,0),"""")"
        .Range("y" & i) = .Range("Y" & i).Value
        .Range("z" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-12])&MONTH(RC[-12]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrCuoi'!R3C1:R999999C5,5,0),"""")"
        .Range("z" & i) = .Range("Z" & i).Value
        .Range("AF" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-18])&MONTH(RC[-18])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AF" & i) = .Range("AF" & i).Value
        .Range("AG" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-32]&""_""&DAY(RC[-19])&MONTH(RC[-19])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AG" & i) = .Range("AG" & i).Value
        .Range("AH" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-33]&""_""&DAY(RC[-20])&MONTH(RC[-20])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AH" & i) = .Range("AH" & i).Value
        .Range("AI" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-34]&""_""&DAY(RC[-21])&MONTH(RC[-21])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AI" & i) = .Range("AI" & i).Value
        .Range("AJ" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-35]&""_""&DAY(RC[-22])&MONTH(RC[-22])&""_""&5,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AJ" & i) = .Range("AJ" & i).Value
        .Range("AK" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-36]&""_""&DAY(RC[-23])&MONTH(RC[-23])&""_""&6,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AK" & i) = .Range("AK" & i).Value
        .Range("AL" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-37]&""_""&DAY(RC[-24])&MONTH(RC[-24])&""_""&7,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AL" & i) = .Range("AL" & i).Value
        .Range("AM" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-38]&""_""&DAY(RC[-25])&MONTH(RC[-25])&""_""&8,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AM" & i) = .Range("AM" & i).Value
        .Range("AN" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-39]&""_""&DAY(RC[-26])&MONTH(RC[-26])&""_""&9,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AN" & i) = .Range("AN" & i).Value
        .Range("AO" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-40]&""_""&DAY(RC[-27])&MONTH(RC[-27])&""_""&10,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AO" & i) = .Range("AO" & i).Value
        .Range("AP" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-41]&""_""&DAY(RC[-28])&MONTH(RC[-28])&""_""&11,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AP" & i) = .Range("AP" & i).Value
        .Range("AQ" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-42]&""_""&DAY(RC[-29])&MONTH(RC[-29])&""_""&12,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AQ" & i) = .Range("AQ" & i).Value
        .Range("AR" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-43]&""_""&DAY(RC[-30])&MONTH(RC[-30])&""_""&13,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AR" & i) = .Range("AR" & i).Value
        .Range("AS" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-44]&""_""&DAY(RC[-31])&MONTH(RC[-31])&""_""&14,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AS" & i) = .Range("AS" & i).Value
        .Range("AT" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-45]&""_""&DAY(RC[-32])&MONTH(RC[-32])&""_""&15,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AT" & i) = .Range("AT" & i).Value
        .Range("AU" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-46]&""_""&DAY(RC[-33])&MONTH(RC[-33])&""_""&16,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AU" & i) = .Range("AU" & i).Value
        .Range("AV" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-34])&MONTH(RC[-34])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
        .Range("AV" & i) = .Range("AV" & i).Value
        .Range("AW" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-26]&""_""&DAY(RC[-35])&MONTH(RC[-35])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
        .Range("AW" & i) = .Range("AW" & i).Value
        .Range("AY" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-28]&""_""&DAY(RC[-37])&MONTH(RC[-37])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
        .Range("AY" & i) = .Range("AY" & i).Value
        .Range("AZ" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-29]&""_""&DAY(RC[-38])&MONTH(RC[-38])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
        .Range("AZ" & i) = .Range("AZ" & i).Value
        .Range("BB" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-40])&MONTH(RC[-40])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
        .Range("BB" & i) = .Range("BB" & i).Value
        .Range("BC" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-32]&""_""&DAY(RC[-41])&MONTH(RC[-41])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
        .Range("BC" & i) = .Range("BC" & i).Value
        .Range("BE" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-34]&""_""&DAY(RC[-43])&MONTH(RC[-43])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
        .Range("BE" & i) = .Range("BE" & i).Value
        .Range("BF" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-35]&""_""&DAY(RC[-44])&MONTH(RC[-44])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
        .Range("BF" & i) = .Range("BF" & i).Value
        .Range("AA" & i).FormulaR1C1 = "=COUNTA(RC[5]:RC[20])-COUNTBLANK(RC[5]:RC[20])"
        .Range("AA" & i) = .Range("AA" & i).Value
        .Range("AB" & i).FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1]*13,"""")"
        .Range("AB" & i) = .Range("AB" & i).Value
        
    End If
Next
End With
Sheets("BDL").Range("A5:BH" & lr + 1).Borders.LineStyle = 1
Sheets("BDL").Range("A" & lr + 1).Select
End Sub
 

File đính kèm

  • TD_SC_Sau_2019.xlsm
    203.3 KB · Đọc: 7
hic1802 đã viết:
Chào mọi người trên GPE,
Em có vấn đề muốn nhờ mọi người giúp đỡ trong việc cải tiến tốc độ xử lý code VBA câu lệnh trên Excel
Em có viết câu lệnh trên VBA các lệnh excel, sau đó chỉ lấy giá trị của nó (bỏ công thức đi để file excel nhẹ, dễ mở khi dữ liệu điền vào lớn)
Tuy nhiên e chạy VBA thấy load chậm quá, nhờ mọi người giúp có cách gì khác để xử lý bài toán này không?
VBA này e dùng record macro + chế thêm

Mã: 
Sub rundata()
On Error Resume Next
Dim i As Long, lr As Long
'Sheets("BDL").Range("A6:BH9999").Borders.LineStyle = 0
lr = Sheets("BDL").Cells(Sheets("BDL").Rows.Count, "A").End(xlUp).Row
If lr <= 4 Then
    MsgBox "Chua co du lieu"
    Exit Sub
End If
With Sheets("BDL")
For i = 5 To lr
    If .Range("BH" & i).Value = "" Then
        .Range("X" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-23]&""_""&DAY(RC[-10])&MONTH(RC[-10]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrDau'!R3C1:R999999C5,5,0),"""")"
        .Range("X" & i) = .Range("X" & i).Value
        .Range("y" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-24]&""_""&DAY(RC[-11])&MONTH(RC[-11]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrGiua'!R3C1:R999999C5,5,0),"""")"
        .Range("y" & i) = .Range("Y" & i).Value
        .Range("z" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-12])&MONTH(RC[-12]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrCuoi'!R3C1:R999999C5,5,0),"""")"
        .Range("z" & i) = .Range("Z" & i).Value
        .Range("AF" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-18])&MONTH(RC[-18])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AF" & i) = .Range("AF" & i).Value
        .Range("AG" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-32]&""_""&DAY(RC[-19])&MONTH(RC[-19])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AG" & i) = .Range("AG" & i).Value
        .Range("AH" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-33]&""_""&DAY(RC[-20])&MONTH(RC[-20])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AH" & i) = .Range("AH" & i).Value
        .Range("AI" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-34]&""_""&DAY(RC[-21])&MONTH(RC[-21])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AI" & i) = .Range("AI" & i).Value
        .Range("AJ" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-35]&""_""&DAY(RC[-22])&MONTH(RC[-22])&""_""&5,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AJ" & i) = .Range("AJ" & i).Value
        .Range("AK" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-36]&""_""&DAY(RC[-23])&MONTH(RC[-23])&""_""&6,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AK" & i) = .Range("AK" & i).Value
        .Range("AL" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-37]&""_""&DAY(RC[-24])&MONTH(RC[-24])&""_""&7,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AL" & i) = .Range("AL" & i).Value
        .Range("AM" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-38]&""_""&DAY(RC[-25])&MONTH(RC[-25])&""_""&8,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AM" & i) = .Range("AM" & i).Value
        .Range("AN" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-39]&""_""&DAY(RC[-26])&MONTH(RC[-26])&""_""&9,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AN" & i) = .Range("AN" & i).Value
        .Range("AO" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-40]&""_""&DAY(RC[-27])&MONTH(RC[-27])&""_""&10,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AO" & i) = .Range("AO" & i).Value
        .Range("AP" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-41]&""_""&DAY(RC[-28])&MONTH(RC[-28])&""_""&11,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AP" & i) = .Range("AP" & i).Value
        .Range("AQ" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-42]&""_""&DAY(RC[-29])&MONTH(RC[-29])&""_""&12,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AQ" & i) = .Range("AQ" & i).Value
        .Range("AR" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-43]&""_""&DAY(RC[-30])&MONTH(RC[-30])&""_""&13,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AR" & i) = .Range("AR" & i).Value
        .Range("AS" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-44]&""_""&DAY(RC[-31])&MONTH(RC[-31])&""_""&14,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AS" & i) = .Range("AS" & i).Value
        .Range("AT" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-45]&""_""&DAY(RC[-32])&MONTH(RC[-32])&""_""&15,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AT" & i) = .Range("AT" & i).Value
        .Range("AU" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-46]&""_""&DAY(RC[-33])&MONTH(RC[-33])&""_""&16,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AU" & i) = .Range("AU" & i).Value
        .Range("AV" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-34])&MONTH(RC[-34])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
        .Range("AV" & i) = .Range("AV" & i).Value
        .Range("AW" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-26]&""_""&DAY(RC[-35])&MONTH(RC[-35])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
        .Range("AW" & i) = .Range("AW" & i).Value
        .Range("AY" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-28]&""_""&DAY(RC[-37])&MONTH(RC[-37])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
        .Range("AY" & i) = .Range("AY" & i).Value
        .Range("AZ" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-29]&""_""&DAY(RC[-38])&MONTH(RC[-38])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
        .Range("AZ" & i) = .Range("AZ" & i).Value
        .Range("BB" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-40])&MONTH(RC[-40])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
        .Range("BB" & i) = .Range("BB" & i).Value
        .Range("BC" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-32]&""_""&DAY(RC[-41])&MONTH(RC[-41])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
        .Range("BC" & i) = .Range("BC" & i).Value
        .Range("BE" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-34]&""_""&DAY(RC[-43])&MONTH(RC[-43])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
        .Range("BE" & i) = .Range("BE" & i).Value
        .Range("BF" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-35]&""_""&DAY(RC[-44])&MONTH(RC[-44])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
        .Range("BF" & i) = .Range("BF" & i).Value
        .Range("AA" & i).FormulaR1C1 = "=COUNTA(RC[5]:RC[20])-COUNTBLANK(RC[5]:RC[20])"
        .Range("AA" & i) = .Range("AA" & i).Value
        .Range("AB" & i).FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1]*13,"""")"
        .Range("AB" & i) = .Range("AB" & i).Value
       
    End If
Next
End With
Sheets("BDL").Range("A5:BH" & lr + 1).Borders.LineStyle = 1
Sheets("BDL").Range("A" & lr + 1).Select
End Sub
Nhấp chuột vào đây để mở rộng...
Từ code mẫu, bạn viết thêm
Mã: 
Sub Tam()
'On Error Resume Next
Dim i As Long, lr As Long
Application.ScreenUpdating = False
With Sheets("BDL")
    lr = .Cells(Sheets("BDL").Rows.Count, "A").End(xlUp).Row
    If lr <= 4 Then MsgBox "Chua co du lieu":    Exit Sub
    
    'Application.AskToUpdateLinks = False
    .Range("X6:X" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-23]&""_""&DAY(RC[-10])&MONTH(RC[-10]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrDau'!R3C1:R999999C5,5,0),"""")"
    .Range("y6:y" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-24]&""_""&DAY(RC[-11])&MONTH(RC[-11]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrGiua'!R3C1:R999999C5,5,0),"""")"
    .Range("z6:z" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-12])&MONTH(RC[-12]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrCuoi'!R3C1:R999999C5,5,0),"""")"
    .Range("AF6:AF" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-18])&MONTH(RC[-18])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    ' làm tiep tuc cho cac lenh khac

    'Application.AskToUpdateLinks = True

    .Range("X6:X" & lr) = .Range("X6:X" & lr).Value
    .Range("y6:y" & lr) = .Range("y6:y" & lr).Value
    .Range("z6:z" & lr) = .Range("z6:z" & lr).Value
    .Range("AF6:AF" & lr) = .Range("AF6:AF" & lr).Value
    ' làm tiep tuc
End With
Application.ScreenUpdating = True
End Sub
 
HieuCD đã viết:
Từ code mẫu, bạn viết thêm
Mã: 
Sub Tam()
'On Error Resume Next
Dim i As Long, lr As Long
Application.ScreenUpdating = False
With Sheets("BDL")
    lr = .Cells(Sheets("BDL").Rows.Count, "A").End(xlUp).Row
    If lr <= 4 Then MsgBox "Chua co du lieu":    Exit Sub
 
    'Application.AskToUpdateLinks = False
    .Range("X6:X" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-23]&""_""&DAY(RC[-10])&MONTH(RC[-10]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrDau'!R3C1:R999999C5,5,0),"""")"
    .Range("y6:y" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-24]&""_""&DAY(RC[-11])&MONTH(RC[-11]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrGiua'!R3C1:R999999C5,5,0),"""")"
    .Range("z6:z" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-12])&MONTH(RC[-12]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrCuoi'!R3C1:R999999C5,5,0),"""")"
    .Range("AF6:AF" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-18])&MONTH(RC[-18])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    ' làm tiep tuc cho cac lenh khac

    'Application.AskToUpdateLinks = True

    .Range("X6:X" & lr) = .Range("X6:X" & lr).Value
    .Range("y6:y" & lr) = .Range("y6:y" & lr).Value
    .Range("z6:z" & lr) = .Range("z6:z" & lr).Value
    .Range("AF6:AF" & lr) = .Range("AF6:AF" & lr).Value
    ' làm tiep tuc
End With
Application.ScreenUpdating = True
End Sub
Nhấp chuột vào đây để mở rộng...
Em sử dụng code của bác chạy không đúng?
Bác kiểm tra lại giúp em với.
Mã: 
Sub Tam()
On Error Resume Next
Dim i As Long, lr As Long
Application.ScreenUpdating = False
With Sheets("BDL")
    lr = .Cells(Sheets("BDL").Rows.Count, "A").End(xlUp).Row
    If lr <= 4 Then MsgBox "Chua co du lieu":    Exit Sub
    Application.AskToUpdateLinks = False
    .Range("X6:X" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-23]&""_""&DAY(RC[-10])&MONTH(RC[-10]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrDau'!R3C1:R999999C5,5,0),"""")"
    .Range("y6:y" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-24]&""_""&DAY(RC[-11])&MONTH(RC[-11]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrGiua'!R3C1:R999999C5,5,0),"""")"
    .Range("z6:z" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-12])&MONTH(RC[-12]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrCuoi'!R3C1:R999999C5,5,0),"""")"
    .Range("AF6:AF" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-18])&MONTH(RC[-18])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AG6:AG" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-32]&""_""&DAY(RC[-19])&MONTH(RC[-19])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AH6:AH" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-33]&""_""&DAY(RC[-20])&MONTH(RC[-20])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AI6:AI" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-34]&""_""&DAY(RC[-21])&MONTH(RC[-21])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AJ6:AJ" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-35]&""_""&DAY(RC[-22])&MONTH(RC[-22])&""_""&5,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AK6:AK" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-36]&""_""&DAY(RC[-23])&MONTH(RC[-23])&""_""&6,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AL6:AL" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-37]&""_""&DAY(RC[-24])&MONTH(RC[-24])&""_""&7,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AM6:AM" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-38]&""_""&DAY(RC[-25])&MONTH(RC[-25])&""_""&8,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AN6:AN" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-39]&""_""&DAY(RC[-26])&MONTH(RC[-26])&""_""&9,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AO6:AO" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-40]&""_""&DAY(RC[-27])&MONTH(RC[-27])&""_""&10,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AP6:AP" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-41]&""_""&DAY(RC[-28])&MONTH(RC[-28])&""_""&11,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AQ6:AQ" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-42]&""_""&DAY(RC[-29])&MONTH(RC[-29])&""_""&12,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AR6:AR" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-43]&""_""&DAY(RC[-30])&MONTH(RC[-30])&""_""&13,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AS6:AS" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-44]&""_""&DAY(RC[-31])&MONTH(RC[-31])&""_""&14,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AT6:AT" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-45]&""_""&DAY(RC[-32])&MONTH(RC[-32])&""_""&15,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AU6:AU" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-46]&""_""&DAY(RC[-33])&MONTH(RC[-33])&""_""&16,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AV6:AV" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-34])&MONTH(RC[-34])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
    .Range("AW6:AW" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-26]&""_""&DAY(RC[-35])&MONTH(RC[-35])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
    .Range("AY6:AW" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-28]&""_""&DAY(RC[-37])&MONTH(RC[-37])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
    .Range("AZ6:AZ" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-29]&""_""&DAY(RC[-38])&MONTH(RC[-38])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
    .Range("BB6:BB" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-40])&MONTH(RC[-40])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
    .Range("BC6:BC" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-32]&""_""&DAY(RC[-41])&MONTH(RC[-41])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
    .Range("BE6:BE" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-34]&""_""&DAY(RC[-43])&MONTH(RC[-43])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
    .Range("BF6:BF" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-35]&""_""&DAY(RC[-44])&MONTH(RC[-44])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
    .Range("AA6:AA" & lr).FormulaR1C1 = "=COUNTA(RC[5]:RC[20])"
    .Range("AB6:AB" & lr).FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1]*13,"""")"
  

    .Range("X6:X" & lr) = .Range("X6:X" & lr).Value
    .Range("y6:y" & lr) = .Range("y6:y" & lr).Value
    .Range("z6:z" & lr) = .Range("z6:z" & lr).Value
    .Range("AF6:AF" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AG6:AG" & lr) = .Range("X6:X" & lr).Value
    .Range("AH6:AH" & lr) = .Range("y6:y" & lr).Value
    .Range("AI6:AI" & lr) = .Range("z6:z" & lr).Value
    .Range("AJ6:AJ" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AK6:AK" & lr) = .Range("X6:X" & lr).Value
    .Range("AL6:AL" & lr) = .Range("y6:y" & lr).Value
    .Range("AM6:AM" & lr) = .Range("z6:z" & lr).Value
    .Range("AN6:AN" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AO6:AO" & lr) = .Range("X6:X" & lr).Value
    .Range("AP6:AP" & lr) = .Range("y6:y" & lr).Value
    .Range("z6:z" & lr) = .Range("z6:z" & lr).Value
    .Range("AF6:AF" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AQ6:AQ" & lr) = .Range("X6:X" & lr).Value
    .Range("AR6:AR" & lr) = .Range("y6:y" & lr).Value
    .Range("AS6:AS" & lr) = .Range("z6:z" & lr).Value
    .Range("AT6:ATF" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AU6:AU" & lr) = .Range("X6:X" & lr).Value
    .Range("AV6:AV" & lr) = .Range("y6:y" & lr).Value
    .Range("AW6:AW" & lr) = .Range("z6:z" & lr).Value
    .Range("AY6:AY" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AZ6:AZ" & lr) = .Range("X6:X" & lr).Value
    .Range("BB6:BB" & lr) = .Range("y6:y" & lr).Value
    .Range("BC6:BC" & lr) = .Range("z6:z" & lr).Value
    '.Range("AA6:AA" & lr) = .Range("AF6:AF" & lr).Value
    '.Range("AB6:AB" & lr) = .Range("AF6:AF" & lr).Value
  
End With
Application.ScreenUpdating = True
Sheets("BDL").Range("A5:BH" & lr + 1).Borders.LineStyle = 1
Sheets("BDL").Range("A" & lr + 1).Select
End Sub
 

File đính kèm

  • TD_SC_Sau_2019.xlsm
    203.3 KB · Đọc: 2
Lần chỉnh sửa cuối:
hic1802 đã viết:
Em sử dụng code của bác chạy không đúng?
Bác kiểm tra lại giúp em với.
Mã: 
Sub Tam()
On Error Resume Next
Dim i As Long, lr As Long
Application.ScreenUpdating = False
With Sheets("BDL")
    lr = .Cells(Sheets("BDL").Rows.Count, "A").End(xlUp).Row
    If lr <= 4 Then MsgBox "Chua co du lieu":    Exit Sub
    Application.AskToUpdateLinks = False
    .Range("X6:X" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-23]&""_""&DAY(RC[-10])&MONTH(RC[-10]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrDau'!R3C1:R999999C5,5,0),"""")"
    .Range("y6:y" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-24]&""_""&DAY(RC[-11])&MONTH(RC[-11]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrGiua'!R3C1:R999999C5,5,0),"""")"
    .Range("z6:z" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-12])&MONTH(RC[-12]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrCuoi'!R3C1:R999999C5,5,0),"""")"
    .Range("AF6:AF" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-18])&MONTH(RC[-18])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AG6:AG" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-32]&""_""&DAY(RC[-19])&MONTH(RC[-19])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AH6:AH" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-33]&""_""&DAY(RC[-20])&MONTH(RC[-20])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AI6:AI" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-34]&""_""&DAY(RC[-21])&MONTH(RC[-21])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AJ6:AJ" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-35]&""_""&DAY(RC[-22])&MONTH(RC[-22])&""_""&5,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AK6:AK" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-36]&""_""&DAY(RC[-23])&MONTH(RC[-23])&""_""&6,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AL6:AL" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-37]&""_""&DAY(RC[-24])&MONTH(RC[-24])&""_""&7,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AM6:AM" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-38]&""_""&DAY(RC[-25])&MONTH(RC[-25])&""_""&8,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AN6:AN" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-39]&""_""&DAY(RC[-26])&MONTH(RC[-26])&""_""&9,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AO6:AO" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-40]&""_""&DAY(RC[-27])&MONTH(RC[-27])&""_""&10,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AP6:AP" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-41]&""_""&DAY(RC[-28])&MONTH(RC[-28])&""_""&11,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AQ6:AQ" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-42]&""_""&DAY(RC[-29])&MONTH(RC[-29])&""_""&12,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AR6:AR" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-43]&""_""&DAY(RC[-30])&MONTH(RC[-30])&""_""&13,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AS6:AS" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-44]&""_""&DAY(RC[-31])&MONTH(RC[-31])&""_""&14,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AT6:AT" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-45]&""_""&DAY(RC[-32])&MONTH(RC[-32])&""_""&15,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AU6:AU" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-46]&""_""&DAY(RC[-33])&MONTH(RC[-33])&""_""&16,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AV6:AV" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-34])&MONTH(RC[-34])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
    .Range("AW6:AW" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-26]&""_""&DAY(RC[-35])&MONTH(RC[-35])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
    .Range("AY6:AW" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-28]&""_""&DAY(RC[-37])&MONTH(RC[-37])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
    .Range("AZ6:AZ" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-29]&""_""&DAY(RC[-38])&MONTH(RC[-38])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
    .Range("BB6:BB" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-40])&MONTH(RC[-40])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
    .Range("BC6:BC" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-32]&""_""&DAY(RC[-41])&MONTH(RC[-41])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
    .Range("BE6:BE" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-34]&""_""&DAY(RC[-43])&MONTH(RC[-43])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
    .Range("BF6:BF" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-35]&""_""&DAY(RC[-44])&MONTH(RC[-44])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
    .Range("AA6:AA" & lr).FormulaR1C1 = "=COUNTA(RC[5]:RC[20])"
    .Range("AB6:AB" & lr).FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1]*13,"""")"
 

    .Range("X6:X" & lr) = .Range("X6:X" & lr).Value
    .Range("y6:y" & lr) = .Range("y6:y" & lr).Value
    .Range("z6:z" & lr) = .Range("z6:z" & lr).Value
    .Range("AF6:AF" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AG6:AG" & lr) = .Range("X6:X" & lr).Value
    .Range("AH6:AH" & lr) = .Range("y6:y" & lr).Value
    .Range("AI6:AI" & lr) = .Range("z6:z" & lr).Value
    .Range("AJ6:AJ" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AK6:AK" & lr) = .Range("X6:X" & lr).Value
    .Range("AL6:AL" & lr) = .Range("y6:y" & lr).Value
    .Range("AM6:AM" & lr) = .Range("z6:z" & lr).Value
    .Range("AN6:AN" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AO6:AO" & lr) = .Range("X6:X" & lr).Value
    .Range("AP6:AP" & lr) = .Range("y6:y" & lr).Value
    .Range("z6:z" & lr) = .Range("z6:z" & lr).Value
    .Range("AF6:AF" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AQ6:AQ" & lr) = .Range("X6:X" & lr).Value
    .Range("AR6:AR" & lr) = .Range("y6:y" & lr).Value
    .Range("AS6:AS" & lr) = .Range("z6:z" & lr).Value
    .Range("AT6:ATF" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AU6:AU" & lr) = .Range("X6:X" & lr).Value
    .Range("AV6:AV" & lr) = .Range("y6:y" & lr).Value
    .Range("AW6:AW" & lr) = .Range("z6:z" & lr).Value
    .Range("AY6:AY" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AZ6:AZ" & lr) = .Range("X6:X" & lr).Value
    .Range("BB6:BB" & lr) = .Range("y6:y" & lr).Value
    .Range("BC6:BC" & lr) = .Range("z6:z" & lr).Value
    '.Range("AA6:AA" & lr) = .Range("AF6:AF" & lr).Value
    '.Range("AB6:AB" & lr) = .Range("AF6:AF" & lr).Value
 
End With
Application.ScreenUpdating = True
Sheets("BDL").Range("A5:BH" & lr + 1).Borders.LineStyle = 1
Sheets("BDL").Range("A" & lr + 1).Select
End Sub
Nhấp chuột vào đây để mở rộng...
Không đúng như thế nào? Mình không có file để kiểm tra
 
hic1802 đã viết:
Chào mọi người trên GPE,
Em có vấn đề muốn nhờ mọi người giúp đỡ trong việc cải tiến tốc độ xử lý code VBA câu lệnh trên Excel
Em có viết câu lệnh trên VBA các lệnh excel, sau đó chỉ lấy giá trị của nó (bỏ công thức đi để file excel nhẹ, dễ mở khi dữ liệu điền vào lớn)
Tuy nhiên e chạy VBA thấy load chậm quá, nhờ mọi người giúp có cách gì khác để xử lý bài toán này không?
VBA này e dùng record macro + chế thêm

Mã: 
Sub rundata()
On Error Resume Next
Dim i As Long, lr As Long
'Sheets("BDL").Range("A6:BH9999").Borders.LineStyle = 0
lr = Sheets("BDL").Cells(Sheets("BDL").Rows.Count, "A").End(xlUp).Row
If lr <= 4 Then
    MsgBox "Chua co du lieu"
    Exit Sub
End If
With Sheets("BDL")
For i = 5 To lr
    If .Range("BH" & i).Value = "" Then
        .Range("X" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-23]&""_""&DAY(RC[-10])&MONTH(RC[-10]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrDau'!R3C1:R999999C5,5,0),"""")"
        .Range("X" & i) = .Range("X" & i).Value
        .Range("y" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-24]&""_""&DAY(RC[-11])&MONTH(RC[-11]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrGiua'!R3C1:R999999C5,5,0),"""")"
        .Range("y" & i) = .Range("Y" & i).Value
        .Range("z" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-12])&MONTH(RC[-12]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrCuoi'!R3C1:R999999C5,5,0),"""")"
        .Range("z" & i) = .Range("Z" & i).Value
        .Range("AF" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-18])&MONTH(RC[-18])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AF" & i) = .Range("AF" & i).Value
        .Range("AG" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-32]&""_""&DAY(RC[-19])&MONTH(RC[-19])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AG" & i) = .Range("AG" & i).Value
        .Range("AH" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-33]&""_""&DAY(RC[-20])&MONTH(RC[-20])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AH" & i) = .Range("AH" & i).Value
        .Range("AI" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-34]&""_""&DAY(RC[-21])&MONTH(RC[-21])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AI" & i) = .Range("AI" & i).Value
        .Range("AJ" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-35]&""_""&DAY(RC[-22])&MONTH(RC[-22])&""_""&5,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AJ" & i) = .Range("AJ" & i).Value
        .Range("AK" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-36]&""_""&DAY(RC[-23])&MONTH(RC[-23])&""_""&6,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AK" & i) = .Range("AK" & i).Value
        .Range("AL" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-37]&""_""&DAY(RC[-24])&MONTH(RC[-24])&""_""&7,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AL" & i) = .Range("AL" & i).Value
        .Range("AM" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-38]&""_""&DAY(RC[-25])&MONTH(RC[-25])&""_""&8,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AM" & i) = .Range("AM" & i).Value
        .Range("AN" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-39]&""_""&DAY(RC[-26])&MONTH(RC[-26])&""_""&9,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AN" & i) = .Range("AN" & i).Value
        .Range("AO" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-40]&""_""&DAY(RC[-27])&MONTH(RC[-27])&""_""&10,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AO" & i) = .Range("AO" & i).Value
        .Range("AP" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-41]&""_""&DAY(RC[-28])&MONTH(RC[-28])&""_""&11,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AP" & i) = .Range("AP" & i).Value
        .Range("AQ" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-42]&""_""&DAY(RC[-29])&MONTH(RC[-29])&""_""&12,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AQ" & i) = .Range("AQ" & i).Value
        .Range("AR" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-43]&""_""&DAY(RC[-30])&MONTH(RC[-30])&""_""&13,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AR" & i) = .Range("AR" & i).Value
        .Range("AS" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-44]&""_""&DAY(RC[-31])&MONTH(RC[-31])&""_""&14,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AS" & i) = .Range("AS" & i).Value
        .Range("AT" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-45]&""_""&DAY(RC[-32])&MONTH(RC[-32])&""_""&15,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AT" & i) = .Range("AT" & i).Value
        .Range("AU" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-46]&""_""&DAY(RC[-33])&MONTH(RC[-33])&""_""&16,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
        .Range("AU" & i) = .Range("AU" & i).Value
        .Range("AV" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-34])&MONTH(RC[-34])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
        .Range("AV" & i) = .Range("AV" & i).Value
        .Range("AW" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-26]&""_""&DAY(RC[-35])&MONTH(RC[-35])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
        .Range("AW" & i) = .Range("AW" & i).Value
        .Range("AY" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-28]&""_""&DAY(RC[-37])&MONTH(RC[-37])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
        .Range("AY" & i) = .Range("AY" & i).Value
        .Range("AZ" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-29]&""_""&DAY(RC[-38])&MONTH(RC[-38])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
        .Range("AZ" & i) = .Range("AZ" & i).Value
        .Range("BB" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-40])&MONTH(RC[-40])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
        .Range("BB" & i) = .Range("BB" & i).Value
        .Range("BC" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-32]&""_""&DAY(RC[-41])&MONTH(RC[-41])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
        .Range("BC" & i) = .Range("BC" & i).Value
        .Range("BE" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-34]&""_""&DAY(RC[-43])&MONTH(RC[-43])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
        .Range("BE" & i) = .Range("BE" & i).Value
        .Range("BF" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-35]&""_""&DAY(RC[-44])&MONTH(RC[-44])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
        .Range("BF" & i) = .Range("BF" & i).Value
        .Range("AA" & i).FormulaR1C1 = "=COUNTA(RC[5]:RC[20])-COUNTBLANK(RC[5]:RC[20])"
        .Range("AA" & i) = .Range("AA" & i).Value
        .Range("AB" & i).FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1]*13,"""")"
        .Range("AB" & i) = .Range("AB" & i).Value
       
    End If
Next
End With
Sheets("BDL").Range("A5:BH" & lr + 1).Borders.LineStyle = 1
Sheets("BDL").Range("A" & lr + 1).Select
End Sub
Nhấp chuột vào đây để mở rộng...
Dùng 1 loạt công thức trên VBA thì khác gì gõ công thức trên excel rồi dán lại theo value, bạn nên đi theo hướng logic không dùng công thức
 
hic1802 đã viết:
Em sử dụng code của bác chạy không đúng?
Bác kiểm tra lại giúp em với.
Mã: 
Sub Tam()
On Error Resume Next
Dim i As Long, lr As Long
Application.ScreenUpdating = False
With Sheets("BDL")
    lr = .Cells(Sheets("BDL").Rows.Count, "A").End(xlUp).Row
    If lr <= 4 Then MsgBox "Chua co du lieu":    Exit Sub
    Application.AskToUpdateLinks = False
    .Range("X6:X" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-23]&""_""&DAY(RC[-10])&MONTH(RC[-10]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrDau'!R3C1:R999999C5,5,0),"""")"
    .Range("y6:y" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-24]&""_""&DAY(RC[-11])&MONTH(RC[-11]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrGiua'!R3C1:R999999C5,5,0),"""")"
    .Range("z6:z" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-12])&MONTH(RC[-12]),'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]BrCuoi'!R3C1:R999999C5,5,0),"""")"
    .Range("AF6:AF" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-18])&MONTH(RC[-18])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AG6:AG" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-32]&""_""&DAY(RC[-19])&MONTH(RC[-19])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AH6:AH" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-33]&""_""&DAY(RC[-20])&MONTH(RC[-20])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AI6:AI" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-34]&""_""&DAY(RC[-21])&MONTH(RC[-21])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AJ6:AJ" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-35]&""_""&DAY(RC[-22])&MONTH(RC[-22])&""_""&5,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AK6:AK" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-36]&""_""&DAY(RC[-23])&MONTH(RC[-23])&""_""&6,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AL6:AL" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-37]&""_""&DAY(RC[-24])&MONTH(RC[-24])&""_""&7,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AM6:AM" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-38]&""_""&DAY(RC[-25])&MONTH(RC[-25])&""_""&8,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AN6:AN" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-39]&""_""&DAY(RC[-26])&MONTH(RC[-26])&""_""&9,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AO6:AO" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-40]&""_""&DAY(RC[-27])&MONTH(RC[-27])&""_""&10,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AP6:AP" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-41]&""_""&DAY(RC[-28])&MONTH(RC[-28])&""_""&11,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AQ6:AQ" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-42]&""_""&DAY(RC[-29])&MONTH(RC[-29])&""_""&12,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AR6:AR" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-43]&""_""&DAY(RC[-30])&MONTH(RC[-30])&""_""&13,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AS6:AS" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-44]&""_""&DAY(RC[-31])&MONTH(RC[-31])&""_""&14,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AT6:AT" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-45]&""_""&DAY(RC[-32])&MONTH(RC[-32])&""_""&15,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AU6:AU" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-46]&""_""&DAY(RC[-33])&MONTH(RC[-33])&""_""&16,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]SoBinh'!R3C1:R999999C3,3,0),"""")"
    .Range("AV6:AV" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-25]&""_""&DAY(RC[-34])&MONTH(RC[-34])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
    .Range("AW6:AW" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-26]&""_""&DAY(RC[-35])&MONTH(RC[-35])&""_""&1,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
    .Range("AY6:AW" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-28]&""_""&DAY(RC[-37])&MONTH(RC[-37])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
    .Range("AZ6:AZ" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-29]&""_""&DAY(RC[-38])&MONTH(RC[-38])&""_""&2,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
    .Range("BB6:BB" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-31]&""_""&DAY(RC[-40])&MONTH(RC[-40])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
    .Range("BC6:BC" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-32]&""_""&DAY(RC[-41])&MONTH(RC[-41])&""_""&3,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
    .Range("BE6:BE" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-34]&""_""&DAY(RC[-43])&MONTH(RC[-43])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,5,0),"""")"
    .Range("BF6:BF" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-35]&""_""&DAY(RC[-44])&MONTH(RC[-44])&""_""&4,'\\192.168.14.1\Honglam2019\SanXuat\9.P2_PX1\2019\SC_TDD_2019\[NhapLieu_TDSau.xlsb]DichDu'!R3C1:R999999C7,6,0),"""")"
    .Range("AA6:AA" & lr).FormulaR1C1 = "=COUNTA(RC[5]:RC[20])"
    .Range("AB6:AB" & lr).FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1]*13,"""")"
 

    .Range("X6:X" & lr) = .Range("X6:X" & lr).Value
    .Range("y6:y" & lr) = .Range("y6:y" & lr).Value
    .Range("z6:z" & lr) = .Range("z6:z" & lr).Value
    .Range("AF6:AF" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AG6:AG" & lr) = .Range("X6:X" & lr).Value
    .Range("AH6:AH" & lr) = .Range("y6:y" & lr).Value
    .Range("AI6:AI" & lr) = .Range("z6:z" & lr).Value
    .Range("AJ6:AJ" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AK6:AK" & lr) = .Range("X6:X" & lr).Value
    .Range("AL6:AL" & lr) = .Range("y6:y" & lr).Value
    .Range("AM6:AM" & lr) = .Range("z6:z" & lr).Value
    .Range("AN6:AN" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AO6:AO" & lr) = .Range("X6:X" & lr).Value
    .Range("AP6:AP" & lr) = .Range("y6:y" & lr).Value
    .Range("z6:z" & lr) = .Range("z6:z" & lr).Value
    .Range("AF6:AF" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AQ6:AQ" & lr) = .Range("X6:X" & lr).Value
    .Range("AR6:AR" & lr) = .Range("y6:y" & lr).Value
    .Range("AS6:AS" & lr) = .Range("z6:z" & lr).Value
    .Range("AT6:ATF" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AU6:AU" & lr) = .Range("X6:X" & lr).Value
    .Range("AV6:AV" & lr) = .Range("y6:y" & lr).Value
    .Range("AW6:AW" & lr) = .Range("z6:z" & lr).Value
    .Range("AY6:AY" & lr) = .Range("AF6:AF" & lr).Value
    .Range("AZ6:AZ" & lr) = .Range("X6:X" & lr).Value
    .Range("BB6:BB" & lr) = .Range("y6:y" & lr).Value
    .Range("BC6:BC" & lr) = .Range("z6:z" & lr).Value
    '.Range("AA6:AA" & lr) = .Range("AF6:AF" & lr).Value
    '.Range("AB6:AB" & lr) = .Range("AF6:AF" & lr).Value
 
End With
Application.ScreenUpdating = True
Sheets("BDL").Range("A5:BH" & lr + 1).Borders.LineStyle = 1
Sheets("BDL").Range("A" & lr + 1).Select
End Sub
Nhấp chuột vào đây để mở rộng...
Bạn gửi file với cả file chạy cùng lên mới kiểm tra được chứ.Mà bạn nói mục đích bạn làm gì luôn.Anh HieuCD viết code mới luôn cho.:D
 
HieuCD đã viết:
Không đúng như thế nào? Mình không có file để kiểm tra
Nhấp chuột vào đây để mở rộng...
Để e nói rõ hơn : em sử dụng vlookup để lấy dữ liệu từ 1 file excel để cùng 1 thư mục trên 1 máy tính được chia sẻ trong mạng Lan (lấy dữ liệu từ sheets NhapLieu sang TD_SC) , vì em chỉ biết dùng lệnh vlookup để chuyển dữ liệu sang.
E gửi a cả 2 file bác xem qua a
Bài đã được tự động gộp:

hoanganhvo2612 đã viết:
Dùng 1 loạt công thức trên VBA thì khác gì gõ công thức trên excel rồi dán lại theo value, bạn nên đi theo hướng logic không dùng công thức
Nhấp chuột vào đây để mở rộng...
em cũng chưa biết cách làm khác, nên em đang muốn hỏi mọi người trên diễn đàn mình
 

File đính kèm

  • NhapLieu_TDSau.xlsb
    83 KB · Đọc: 5
  • TD_SC_Sau_2019.xlsm
    205.4 KB · Đọc: 6
hic1802 đã viết:
Để e nói rõ hơn : em sử dụng vlookup để lấy dữ liệu từ 1 file excel để cùng 1 thư mục trên 1 máy tính được chia sẻ trong mạng Lan (lấy dữ liệu từ sheets NhapLieu sang TD_SC) , vì em chỉ biết dùng lệnh vlookup để chuyển dữ liệu sang.
E gửi a cả 2 file bác xem qua a
Bài đã được tự động gộp:


em cũng chưa biết cách làm khác, nên em đang muốn hỏi mọi người trên diễn đàn mình
Nhấp chuột vào đây để mở rộng...
File ở cùng 1 thư mục, trên mạng chưa thử nên không biết chạy được không
Mã: 
Sub Gop_Data()
  Dim cn As Object, sqlStr As String, fileNguon As String
  Dim Dic As Object, iKey As String
  Dim sArr(), tArr(), Res(), Res2(), Res3(), Res4(), S
  Dim i As Long, ik As Long, sRow As Long, j As Long
  With Sheets("BDL")
    i = .Range("A1000000").End(xlUp).Row
    If i < 5 Then MsgBox ("Khong co du lieu"): Exit Sub
    tArr = .Range("A5:W" & i).Value
    sRow = UBound(tArr)
  End With
  Set Dic = CreateObject("scripting.dictionary")
  For i = 1 To sRow
    If Len(tArr(i, 1)) > 0 And TypeName(tArr(i, 14)) = "Date" Then
      iKey = tArr(i, 1) & "#" & Day(tArr(i, 14)) & "#" & Month(tArr(i, 14))
      Dic.Item(iKey) = Dic.Item(iKey) & "," & i
    End If
    If Len(tArr(i, 23)) > 0 And TypeName(tArr(i, 14)) = "Date" Then
      iKey = tArr(i, 23) & "#" & Day(tArr(i, 14)) & "#" & Month(tArr(i, 14))
      Dic.Item(iKey) = Dic.Item(iKey) & "," & i
    End If
  Next i
  fileNguon = ThisWorkbook.Path & "\NhapLieu_TDSau.xlsb"
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileNguon & ";Extended Properties=""Excel 12.0;HDR=No"";"
 
  ReDim Res(1 To sRow, 1 To 3)
  sqlStr = "Select * From [BrDau$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), 1) = sArr(i, 4)
        Next j
      End If
    End If
  Next i
 
  sqlStr = "Select * From [BrGiua$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), 2) = sArr(i, 4)
        Next j
      End If
    End If
  Next i
 
  sqlStr = "Select * From [BrCuoi$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), 3) = sArr(i, 4)
        Next j
      End If
    End If
  Next i
  Sheets("BDL").Range("X5:Z5").Resize(sRow) = Res
 
  ReDim Res(1 To sRow, 1 To 16)
  sqlStr = "Select * From [SoBinh$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), sArr(i, 4)) = sArr(i, 2)
        Next j
      End If
    End If
  Next i
  Sheets("BDL").Range("AF5:AU5").Resize(sRow) = Res
 
  ReDim Res(1 To sRow, 1 To 2)
  ReDim Res2(1 To sRow, 1 To 2)
  ReDim Res3(1 To sRow, 1 To 2)
  ReDim Res4(1 To sRow, 1 To 2)
  sqlStr = "Select * From [DichDu$B3:G1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  cn.Close:  Set cn = Nothing
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = "D" & sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          ik = CLng(S(j))
          If sArr(i, 6) = 1 Then
            Res(ik, 1) = sArr(i, 4): Res(ik, 2) = sArr(i, 5)
          ElseIf sArr(i, 6) = 2 Then
            Res2(ik, 1) = sArr(i, 4): Res2(ik, 2) = sArr(i, 5)
          ElseIf sArr(i, 6) = 3 Then
            Res3(ik, 1) = sArr(i, 4): Res3(ik, 2) = sArr(i, 5)
          ElseIf sArr(i, 6) = 4 Then
            Res4(ik, 1) = sArr(i, 4): Res4(ik, 2) = sArr(i, 5)
          End If
        Next j
      End If
    End If
  Next i
  Sheets("BDL").Range("AV5:AW5").Resize(sRow) = Res
  Sheets("BDL").Range("AY5:AZ5").Resize(sRow) = Res2
  Sheets("BDL").Range("BB5:BC5").Resize(sRow) = Res3
  Sheets("BDL").Range("BE5:BF5").Resize(sRow) = Res4
  Set Dic = Nothing
End Sub

Private Function ADO_ToArray(ByRef cn, ByVal sqlStr As String) As Variant
    Dim sArr, Res(), i As Long, j As Long
    sArr = cn.Execute(sqlStr).GetRows
    ReDim Res(1 To UBound(sArr, 2) + 1, 1 To UBound(sArr, 1) + 1)
    For i = LBound(sArr, 2) To UBound(sArr, 2)
        For j = LBound(sArr, 1) To UBound(sArr, 1)
            Res(i + 1, j + 1) = sArr(j, i)
        Next j
    Next i
    ADO_ToArray = Res
End Function
 

File đính kèm

  • TD_SC_Sau_2019 (1).xlsm
    194.8 KB · Đọc: 8
hic1802 đã viết:
Để e nói rõ hơn : em sử dụng vlookup để lấy dữ liệu từ 1 file excel để cùng 1 thư mục trên 1 máy tính được chia sẻ trong mạng Lan (lấy dữ liệu từ sheets NhapLieu sang TD_SC) , vì em chỉ biết dùng lệnh vlookup để chuyển dữ liệu sang.
E gửi a cả 2 file bác xem qua a
em cũng chưa biết cách làm khác, nên em đang muốn hỏi mọi người trên diễn đàn mình
Nhấp chuột vào đây để mở rộng...
Sao không nghĩ theo phương án khác là lấy dữ liệu sheets NhapLieu sang TD_SC vào 1 sheet nào đó của File đang sử dụng rồi muốn tra cái gì thì tra sẽ dễ dàng hơn là tra qua File chia sẽ trong mạng Lan, nếu có nhiều người dùng hoặc rớt mạng thì tèo.
 
be09 đã viết:
Sao không nghĩ theo phương án khác là lấy dữ liệu sheets NhapLieu sang TD_SC vào 1 sheet nào đó của File đang sử dụng rồi muốn tra cái gì thì tra sẽ dễ dàng hơn là tra qua File chia sẽ trong mạng Lan, nếu có nhiều người dùng hoặc rớt mạng thì tèo.
Nhấp chuột vào đây để mở rộng...
cái này có nghĩa là mình tạo 2 nút : cập nhật dữ liệu về bảng làm trước, sau đó lại lấy dữ liệu sang sheet đang dùng đúng không a?
Thế thì có khác gì nhau đâu a? Ý em nói là e đang dùng VBA để lấy giá trị của công thức excel thôi, chứ không dùng trực tiếp công thức excel trên bảng tác nghiệp (do có công thức excel sẽ rất năng file, khó mở)
 
HieuCD đã viết:
File ở cùng 1 thư mục, trên mạng chưa thử nên không biết chạy được không
Mã: 
Sub Gop_Data()
  Dim cn As Object, sqlStr As String, fileNguon As String
  Dim Dic As Object, iKey As String
  Dim sArr(), tArr(), Res(), Res2(), Res3(), Res4(), S
  Dim i As Long, ik As Long, sRow As Long, j As Long
  With Sheets("BDL")
    i = .Range("A1000000").End(xlUp).Row
    If i < 5 Then MsgBox ("Khong co du lieu"): Exit Sub
    tArr = .Range("A5:W" & i).Value
    sRow = UBound(tArr)
  End With
  Set Dic = CreateObject("scripting.dictionary")
  For i = 1 To sRow
    If Len(tArr(i, 1)) > 0 And TypeName(tArr(i, 14)) = "Date" Then
      iKey = tArr(i, 1) & "#" & Day(tArr(i, 14)) & "#" & Month(tArr(i, 14))
      Dic.Item(iKey) = Dic.Item(iKey) & "," & i
    End If
    If Len(tArr(i, 23)) > 0 And TypeName(tArr(i, 14)) = "Date" Then
      iKey = tArr(i, 23) & "#" & Day(tArr(i, 14)) & "#" & Month(tArr(i, 14))
      Dic.Item(iKey) = Dic.Item(iKey) & "," & i
    End If
  Next i
  fileNguon = ThisWorkbook.Path & "\NhapLieu_TDSau.xlsb"
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileNguon & ";Extended Properties=""Excel 12.0;HDR=No"";"

  ReDim Res(1 To sRow, 1 To 3)
  sqlStr = "Select * From [BrDau$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), 1) = sArr(i, 4)
        Next j
      End If
    End If
  Next i

  sqlStr = "Select * From [BrGiua$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), 2) = sArr(i, 4)
        Next j
      End If
    End If
  Next i

  sqlStr = "Select * From [BrCuoi$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), 3) = sArr(i, 4)
        Next j
      End If
    End If
  Next i
  Sheets("BDL").Range("X5:Z5").Resize(sRow) = Res

  ReDim Res(1 To sRow, 1 To 16)
  sqlStr = "Select * From [SoBinh$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), sArr(i, 4)) = sArr(i, 2)
        Next j
      End If
    End If
  Next i
  Sheets("BDL").Range("AF5:AU5").Resize(sRow) = Res

  ReDim Res(1 To sRow, 1 To 2)
  ReDim Res2(1 To sRow, 1 To 2)
  ReDim Res3(1 To sRow, 1 To 2)
  ReDim Res4(1 To sRow, 1 To 2)
  sqlStr = "Select * From [DichDu$B3:G1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  cn.Close:  Set cn = Nothing
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = "D" & sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          ik = CLng(S(j))
          If sArr(i, 6) = 1 Then
            Res(ik, 1) = sArr(i, 4): Res(ik, 2) = sArr(i, 5)
          ElseIf sArr(i, 6) = 2 Then
            Res2(ik, 1) = sArr(i, 4): Res2(ik, 2) = sArr(i, 5)
          ElseIf sArr(i, 6) = 3 Then
            Res3(ik, 1) = sArr(i, 4): Res3(ik, 2) = sArr(i, 5)
          ElseIf sArr(i, 6) = 4 Then
            Res4(ik, 1) = sArr(i, 4): Res4(ik, 2) = sArr(i, 5)
          End If
        Next j
      End If
    End If
  Next i
  Sheets("BDL").Range("AV5:AW5").Resize(sRow) = Res
  Sheets("BDL").Range("AY5:AZ5").Resize(sRow) = Res2
  Sheets("BDL").Range("BB5:BC5").Resize(sRow) = Res3
  Sheets("BDL").Range("BE5:BF5").Resize(sRow) = Res4
  Set Dic = Nothing
End Sub

Private Function ADO_ToArray(ByRef cn, ByVal sqlStr As String) As Variant
    Dim sArr, Res(), i As Long, j As Long
    sArr = cn.Execute(sqlStr).GetRows
    ReDim Res(1 To UBound(sArr, 2) + 1, 1 To UBound(sArr, 1) + 1)
    For i = LBound(sArr, 2) To UBound(sArr, 2)
        For j = LBound(sArr, 1) To UBound(sArr, 1)
            Res(i + 1, j + 1) = sArr(j, i)
        Next j
    Next i
    ADO_ToArray = Res
End Function
Nhấp chuột vào đây để mở rộng...
Nó chạy ko ra kết quả các cột "AA:AB"
Công thức : Cột AA = counta("AF:AU")
cột AB = AA*13
Code bác khó hiểu quá, e tính bắt trước mà không làm được :v
code bác chạy siêu nhanh a :D
 
Lần chỉnh sửa cuối:
HieuCD đã viết:
Code viết theo hàm Vlookup trong file gốc, và đâu thấy có kết quả ở các cột nầy
Nhấp chuột vào đây để mở rộng...
vâng em mới check lại file em gửi lên có tí khác với file excel đang dùng ạ,
Bây giờ em muốn đặt thêm công thức ở 2 côt AA và AB như trên thì phải chèn thêm code như thế nào ạ???
Bây giờ e cũng không chèn kiểu VBA công thức excel như cũ được?
Nhờ anh giúp thêm công thức 2 cột AA và AB a
 
hic1802 đã viết:
vâng em mới check lại file em gửi lên có tí khác với file excel đang dùng ạ,
Bây giờ em muốn đặt thêm công thức ở 2 côt AA và AB như trên thì phải chèn thêm code như thế nào ạ???
Bây giờ e cũng không chèn kiểu VBA công thức excel như cũ được?
Nhờ anh giúp thêm công thức 2 cột AA và AB a
Nhấp chuột vào đây để mở rộng...
Thêm 2 cột AA:AB
Mã: 
Sub Gop_Data()
  Dim cn As Object, sqlStr As String, fileNguon As String
  Dim Dic As Object, iKey As String
  Dim sArr(), tArr(), Res(), Res2(), Res3(), Res4(), S
  Dim i As Long, ik As Long, sRow As Long, j As Long
  With Sheets("BDL")
    i = .Range("A1000000").End(xlUp).Row
    If i < 5 Then MsgBox ("Khong co du lieu"): Exit Sub
    tArr = .Range("A5:W" & i).Value
    sRow = UBound(tArr)
  End With
  Set Dic = CreateObject("scripting.dictionary")
  For i = 1 To sRow
    If Len(tArr(i, 1)) > 0 And TypeName(tArr(i, 14)) = "Date" Then
      iKey = tArr(i, 1) & "#" & Day(tArr(i, 14)) & "#" & Month(tArr(i, 14))
      Dic.Item(iKey) = Dic.Item(iKey) & "," & i
    End If
    If Len(tArr(i, 23)) > 0 And TypeName(tArr(i, 14)) = "Date" Then
      iKey = tArr(i, 23) & "#" & Day(tArr(i, 14)) & "#" & Month(tArr(i, 14))
      Dic.Item(iKey) = Dic.Item(iKey) & "," & i
    End If
  Next i
  fileNguon = ThisWorkbook.Path & "\NhapLieu_TDSau.xlsb"
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileNguon & ";Extended Properties=""Excel 12.0;HDR=No"";"
 
  ReDim Res(1 To sRow, 1 To 3)
  sqlStr = "Select * From [BrDau$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), 1) = sArr(i, 4)
        Next j
      End If
    End If
  Next i
 
  sqlStr = "Select * From [BrGiua$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), 2) = sArr(i, 4)
        Next j
      End If
    End If
  Next i
 
  sqlStr = "Select * From [BrCuoi$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), 3) = sArr(i, 4)
        Next j
      End If
    End If
  Next i
  Sheets("BDL").Range("X5:Z5").Resize(sRow) = Res
 
  ReDim Res(1 To sRow, 1 To 16) 'cot "AF:AU"
  sqlStr = "Select * From [SoBinh$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), sArr(i, 4)) = sArr(i, 2)
        Next j
      End If
    End If
  Next i
  Sheets("BDL").Range("AF5:AU5").Resize(sRow) = Res
 
  ReDim Res2(1 To sRow, 1 To 2) 'Cot "AA:AB"
  For i = 1 To sRow
    For j = 1 To UBound(Res, 2)
      If Len(Res(i, j)) > 0 Then Res2(i, 1) = Res2(i, 1) + 1
    Next j
    If Len(Res2(i, 1)) > 0 Then Res2(i, 2) = Res2(i, 1) * 13
  Next i
  Sheets("BDL").Range("AA5:AB5").Resize(sRow) = Res2
 
  ReDim Res(1 To sRow, 1 To 2)
  ReDim Res2(1 To sRow, 1 To 2)
  ReDim Res3(1 To sRow, 1 To 2)
  ReDim Res4(1 To sRow, 1 To 2)
  sqlStr = "Select * From [DichDu$B3:G1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  cn.Close:  Set cn = Nothing
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = "D" & sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          ik = CLng(S(j))
          If sArr(i, 6) = 1 Then
            Res(ik, 1) = sArr(i, 4): Res(ik, 2) = sArr(i, 5)
          ElseIf sArr(i, 6) = 2 Then
            Res2(ik, 1) = sArr(i, 4): Res2(ik, 2) = sArr(i, 5)
          ElseIf sArr(i, 6) = 3 Then
            Res3(ik, 1) = sArr(i, 4): Res3(ik, 2) = sArr(i, 5)
          ElseIf sArr(i, 6) = 4 Then
            Res4(ik, 1) = sArr(i, 4): Res4(ik, 2) = sArr(i, 5)
          End If
        Next j
      End If
    End If
  Next i
  Sheets("BDL").Range("AV5:AW5").Resize(sRow) = Res
  Sheets("BDL").Range("AY5:AZ5").Resize(sRow) = Res2
  Sheets("BDL").Range("BB5:BC5").Resize(sRow) = Res3
  Sheets("BDL").Range("BE5:BF5").Resize(sRow) = Res4
  Set Dic = Nothing
End Sub
 
HieuCD đã viết:
Thêm 2 cột AA:AB
Mã: 
Sub Gop_Data()
  Dim cn As Object, sqlStr As String, fileNguon As String
  Dim Dic As Object, iKey As String
  Dim sArr(), tArr(), Res(), Res2(), Res3(), Res4(), S
  Dim i As Long, ik As Long, sRow As Long, j As Long
  With Sheets("BDL")
    i = .Range("A1000000").End(xlUp).Row
    If i < 5 Then MsgBox ("Khong co du lieu"): Exit Sub
    tArr = .Range("A5:W" & i).Value
    sRow = UBound(tArr)
  End With
  Set Dic = CreateObject("scripting.dictionary")
  For i = 1 To sRow
    If Len(tArr(i, 1)) > 0 And TypeName(tArr(i, 14)) = "Date" Then
      iKey = tArr(i, 1) & "#" & Day(tArr(i, 14)) & "#" & Month(tArr(i, 14))
      Dic.Item(iKey) = Dic.Item(iKey) & "," & i
    End If
    If Len(tArr(i, 23)) > 0 And TypeName(tArr(i, 14)) = "Date" Then
      iKey = tArr(i, 23) & "#" & Day(tArr(i, 14)) & "#" & Month(tArr(i, 14))
      Dic.Item(iKey) = Dic.Item(iKey) & "," & i
    End If
  Next i
  fileNguon = ThisWorkbook.Path & "\NhapLieu_TDSau.xlsb"
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileNguon & ";Extended Properties=""Excel 12.0;HDR=No"";"

  ReDim Res(1 To sRow, 1 To 3)
  sqlStr = "Select * From [BrDau$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), 1) = sArr(i, 4)
        Next j
      End If
    End If
  Next i

  sqlStr = "Select * From [BrGiua$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), 2) = sArr(i, 4)
        Next j
      End If
    End If
  Next i

  sqlStr = "Select * From [BrCuoi$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), 3) = sArr(i, 4)
        Next j
      End If
    End If
  Next i
  Sheets("BDL").Range("X5:Z5").Resize(sRow) = Res

  ReDim Res(1 To sRow, 1 To 16) 'cot "AF:AU"
  sqlStr = "Select * From [SoBinh$B3:E1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          Res(CLng(S(j)), sArr(i, 4)) = sArr(i, 2)
        Next j
      End If
    End If
  Next i
  Sheets("BDL").Range("AF5:AU5").Resize(sRow) = Res

  ReDim Res2(1 To sRow, 1 To 2) 'Cot "AA:AB"
  For i = 1 To sRow
    For j = 1 To UBound(Res, 2)
      If Len(Res(i, j)) > 0 Then Res2(i, 1) = Res2(i, 1) + 1
    Next j
    If Len(Res2(i, 1)) > 0 Then Res2(i, 2) = Res2(i, 1) * 13
  Next i
  Sheets("BDL").Range("AA5:AB5").Resize(sRow) = Res2

  ReDim Res(1 To sRow, 1 To 2)
  ReDim Res2(1 To sRow, 1 To 2)
  ReDim Res3(1 To sRow, 1 To 2)
  ReDim Res4(1 To sRow, 1 To 2)
  sqlStr = "Select * From [DichDu$B3:G1000000]  where f1 is not null"
  sArr = ADO_ToArray(cn, sqlStr)
  cn.Close:  Set cn = Nothing
  For i = 1 To UBound(sArr)
    If Len(sArr(i, 3)) > 0 And TypeName(sArr(i, 1)) = "Date" Then
      iKey = "D" & sArr(i, 3) & "#" & Day(sArr(i, 1)) & "#" & Month(sArr(i, 1))
      If Len(Dic.Item(iKey)) > 0 Then
        S = Split(Dic.Item(iKey), ",")
        For j = 1 To UBound(S)
          ik = CLng(S(j))
          If sArr(i, 6) = 1 Then
            Res(ik, 1) = sArr(i, 4): Res(ik, 2) = sArr(i, 5)
          ElseIf sArr(i, 6) = 2 Then
            Res2(ik, 1) = sArr(i, 4): Res2(ik, 2) = sArr(i, 5)
          ElseIf sArr(i, 6) = 3 Then
            Res3(ik, 1) = sArr(i, 4): Res3(ik, 2) = sArr(i, 5)
          ElseIf sArr(i, 6) = 4 Then
            Res4(ik, 1) = sArr(i, 4): Res4(ik, 2) = sArr(i, 5)
          End If
        Next j
      End If
    End If
  Next i
  Sheets("BDL").Range("AV5:AW5").Resize(sRow) = Res
  Sheets("BDL").Range("AY5:AZ5").Resize(sRow) = Res2
  Sheets("BDL").Range("BB5:BC5").Resize(sRow) = Res3
  Sheets("BDL").Range("BE5:BF5").Resize(sRow) = Res4
  Set Dic = Nothing
End Sub
Nhấp chuột vào đây để mở rộng...
bác @HieuCD ơi, đối với câu lệnh có chứa if(đk,iferror(vlookup(...),giatri),giatri1) thì phải viết code như nào ạ???
 
HieuCD đã viết:
iferror(vlookup(...) : Dùng Dic
Còn lại dùng mệnh đề If ... End If
Nhấp chuột vào đây để mở rộng...
bác @HieuCD ơi em muốn thay các câu lệnh excel như phía dưới về VBA kiểu dic và if and if như bác nói trên thì như thế nào ạ?
Mã: 
Sub laydl()
Dim i As Long
With Sheets("DuLieu")
    For i = 4 To 291
        .Range("C" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1]&""_""&DAY(RC[-2])&MONTH(RC[-2]),BrDau!R3C1:R999998C5,5,0),"""")"
        .Range("d" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2]&""_""&DAY(RC[-3])&MONTH(RC[-3]),BrGiua!R3C1:R999999C5,5,0),"""")"
        .Range("e" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3]&""_""&DAY(RC[-4])&MONTH(RC[-4]),BrCuoi!R3C1:R999999C5,5,0),"""")"
        .Range("f" & i).FormulaR1C1 = "=COUNT(RC[2]:RC[17])"
        .Range("h" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-6]&""_""&DAY(RC[-7])&MONTH(RC[-7])&""_""&1,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("i" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-7]&""_""&DAY(RC[-8])&MONTH(RC[-8])&""_""&2,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("j" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-8]&""_""&DAY(RC[-9])&MONTH(RC[-9])&""_""&3,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("k" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-9]&""_""&DAY(RC[-10])&MONTH(RC[-10])&""_""&4,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("l" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-10]&""_""&DAY(RC[-11])&MONTH(RC[-11])&""_""&5,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("m" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-11]&""_""&DAY(RC[-12])&MONTH(RC[-12])&""_""&6,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("n" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-12]&""_""&DAY(RC[-13])&MONTH(RC[-13])&""_""&7,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("o" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-13]&""_""&DAY(RC[-14])&MONTH(RC[-14])&""_""&8,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("p" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-14]&""_""&DAY(RC[-15])&MONTH(RC[-15])&""_""&9,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("q" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-15]&""_""&DAY(RC[-16])&MONTH(RC[-16])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("r" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-16]&""_""&DAY(RC[-17])&MONTH(RC[-17])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("s" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-17]&""_""&DAY(RC[-18])&MONTH(RC[-18])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("t" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-18]&""_""&DAY(RC[-19])&MONTH(RC[-19])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("u" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-19]&""_""&DAY(RC[-20])&MONTH(RC[-20])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("v" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-20]&""_""&DAY(RC[-21])&MONTH(RC[-21])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("w" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-21]&""_""&DAY(RC[-22])&MONTH(RC[-22])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
        
        .Range("y" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1]&""_""&DAY(RC[-24])&MONTH(RC[-24])&""_""&1,DichDu!R3C1:R999999C6,5,0),"""")"
        .Range("z" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2]&""_""&DAY(RC[-25])&MONTH(RC[-25])&""_""&1,DichDu!R3C1:R999999C6,6,0),"""")"
        .Range("aa" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3]&""_""&DAY(RC[-26])&MONTH(RC[-26])&""_""&2,DichDu!R3C1:R999999C6,5,0),"""")"
        .Range("ab" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-4]&""_""&DAY(RC[-27])&MONTH(RC[-27])&""_""&2,DichDu!R3C1:R999999C6,6,0),"""")"
        .Range("ac" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-5]&""_""&DAY(RC[-28])&MONTH(RC[-28])&""_""&3,DichDu!R3C1:R999999C6,5,0),"""")"
        .Range("ad" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-6]&""_""&DAY(RC[-29])&MONTH(RC[-29])&""_""&3,DichDu!R3C1:R999999C6,6,0),"""")"
        
        '.Range("C" & i) = .Range("C" & i).Value
        '.Range("d" & i) = .Range("d" & i).Value
        '.Range("e" & i) = .Range("e" & i).Value
        '.Range("f" & i) = .Range("f" & i).Value
        '.Range("h" & i) = .Range("h" & i).Value
        '.Range("i" & i) = .Range("i" & i).Value
        '.Range("j" & i) = .Range("j" & i).Value
        '.Range("k" & i) = .Range("k" & i).Value
        '.Range("l" & i) = .Range("l" & i).Value
        '.Range("m" & i) = .Range("m" & i).Value
        '.Range("n" & i) = .Range("n" & i).Value
        '.Range("o" & i) = .Range("o" & i).Value
        '.Range("p" & i) = .Range("p" & i).Value
        '.Range("q" & i) = .Range("q" & i).Value
        '.Range("r" & i) = .Range("r" & i).Value
        '.Range("s" & i) = .Range("s" & i).Value
        '.Range("t" & i) = .Range("t" & i).Value
        '.Range("u" & i) = .Range("u" & i).Value
        '.Range("v" & i) = .Range("v" & i).Value
        '.Range("w" & i) = .Range("w" & i).Value
        
        '.Range("y" & i) = .Range("y" & i).Value
        '.Range("z" & i) = .Range("z" & i).Value
        '.Range("aa" & i) = .Range("aa" & i).Value
        '.Range("ab" & i) = .Range("ab" & i).Value
        '.Range("ac" & i) = .Range("ac" & i).Value
        '.Range("ad" & i) = .Range("ad" & i).Value
    Next
End With
ThisWorkbook.Save
End Sub

bác xem ở sheets "DULIEU" nút "LayDL"
Em dùng record thì load dữ liệu rất lâu
 

File đính kèm

  • NhapLieu_TDSau.xlsb
    89.4 KB · Đọc: 6
hic1802 đã viết:
bác @HieuCD ơi em muốn thay các câu lệnh excel như phía dưới về VBA kiểu dic và if and if như bác nói trên thì như thế nào ạ?
Mã: 
Sub laydl()
Dim i As Long
With Sheets("DuLieu")
    For i = 4 To 291
        .Range("C" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1]&""_""&DAY(RC[-2])&MONTH(RC[-2]),BrDau!R3C1:R999998C5,5,0),"""")"
        .Range("d" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2]&""_""&DAY(RC[-3])&MONTH(RC[-3]),BrGiua!R3C1:R999999C5,5,0),"""")"
        .Range("e" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3]&""_""&DAY(RC[-4])&MONTH(RC[-4]),BrCuoi!R3C1:R999999C5,5,0),"""")"
        .Range("f" & i).FormulaR1C1 = "=COUNT(RC[2]:RC[17])"
        .Range("h" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-6]&""_""&DAY(RC[-7])&MONTH(RC[-7])&""_""&1,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("i" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-7]&""_""&DAY(RC[-8])&MONTH(RC[-8])&""_""&2,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("j" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-8]&""_""&DAY(RC[-9])&MONTH(RC[-9])&""_""&3,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("k" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-9]&""_""&DAY(RC[-10])&MONTH(RC[-10])&""_""&4,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("l" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-10]&""_""&DAY(RC[-11])&MONTH(RC[-11])&""_""&5,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("m" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-11]&""_""&DAY(RC[-12])&MONTH(RC[-12])&""_""&6,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("n" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-12]&""_""&DAY(RC[-13])&MONTH(RC[-13])&""_""&7,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("o" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-13]&""_""&DAY(RC[-14])&MONTH(RC[-14])&""_""&8,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("p" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-14]&""_""&DAY(RC[-15])&MONTH(RC[-15])&""_""&9,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("q" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-15]&""_""&DAY(RC[-16])&MONTH(RC[-16])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("r" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-16]&""_""&DAY(RC[-17])&MONTH(RC[-17])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("s" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-17]&""_""&DAY(RC[-18])&MONTH(RC[-18])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("t" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-18]&""_""&DAY(RC[-19])&MONTH(RC[-19])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("u" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-19]&""_""&DAY(RC[-20])&MONTH(RC[-20])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("v" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-20]&""_""&DAY(RC[-21])&MONTH(RC[-21])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
        .Range("w" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-21]&""_""&DAY(RC[-22])&MONTH(RC[-22])&""_""&10,SoBinh!R3C1:R999999C5,3,0),"""")"
       
        .Range("y" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1]&""_""&DAY(RC[-24])&MONTH(RC[-24])&""_""&1,DichDu!R3C1:R999999C6,5,0),"""")"
        .Range("z" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2]&""_""&DAY(RC[-25])&MONTH(RC[-25])&""_""&1,DichDu!R3C1:R999999C6,6,0),"""")"
        .Range("aa" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3]&""_""&DAY(RC[-26])&MONTH(RC[-26])&""_""&2,DichDu!R3C1:R999999C6,5,0),"""")"
        .Range("ab" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-4]&""_""&DAY(RC[-27])&MONTH(RC[-27])&""_""&2,DichDu!R3C1:R999999C6,6,0),"""")"
        .Range("ac" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-5]&""_""&DAY(RC[-28])&MONTH(RC[-28])&""_""&3,DichDu!R3C1:R999999C6,5,0),"""")"
        .Range("ad" & i).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-6]&""_""&DAY(RC[-29])&MONTH(RC[-29])&""_""&3,DichDu!R3C1:R999999C6,6,0),"""")"
       
        '.Range("C" & i) = .Range("C" & i).Value
        '.Range("d" & i) = .Range("d" & i).Value
        '.Range("e" & i) = .Range("e" & i).Value
        '.Range("f" & i) = .Range("f" & i).Value
        '.Range("h" & i) = .Range("h" & i).Value
        '.Range("i" & i) = .Range("i" & i).Value
        '.Range("j" & i) = .Range("j" & i).Value
        '.Range("k" & i) = .Range("k" & i).Value
        '.Range("l" & i) = .Range("l" & i).Value
        '.Range("m" & i) = .Range("m" & i).Value
        '.Range("n" & i) = .Range("n" & i).Value
        '.Range("o" & i) = .Range("o" & i).Value
        '.Range("p" & i) = .Range("p" & i).Value
        '.Range("q" & i) = .Range("q" & i).Value
        '.Range("r" & i) = .Range("r" & i).Value
        '.Range("s" & i) = .Range("s" & i).Value
        '.Range("t" & i) = .Range("t" & i).Value
        '.Range("u" & i) = .Range("u" & i).Value
        '.Range("v" & i) = .Range("v" & i).Value
        '.Range("w" & i) = .Range("w" & i).Value
       
        '.Range("y" & i) = .Range("y" & i).Value
        '.Range("z" & i) = .Range("z" & i).Value
        '.Range("aa" & i) = .Range("aa" & i).Value
        '.Range("ab" & i) = .Range("ab" & i).Value
        '.Range("ac" & i) = .Range("ac" & i).Value
        '.Range("ad" & i) = .Range("ad" & i).Value
    Next
End With
ThisWorkbook.Save
End Sub

bác xem ở sheets "DULIEU" nút "LayDL"
Em dùng record thì load dữ liệu rất lâu
Nhấp chuột vào đây để mở rộng...
Cột X là gì vậy? chỉ xử lý tạm cột X
Mã: 
Sub LayDulieu()
  Dim sArr(), tArr(), Res(), Dic As Object
  Dim i As Long, ik As Long, sRow As Long, eRow As Long
 
  Set Dic = CreateObject("scripting.dictionary")
  With Sheets("DuLieu")
    eRow = .Range("B1000000").End(xlUp).Row
    If eRow < 4 Then Exit Sub
    tArr = .Range("A4:X" & eRow).Value
  End With
  sRow = UBound(tArr)
  ReDim Res(1 To sRow, 1 To 3)
  For i = 1 To sRow
    Dic.Item(tArr(i, 1) & "#" & tArr(i, 2)) = i
    'Dic.Item(tArr(i, 1) & "#" & tArr(i, 24)) = i
  Next i
  With Sheets("BrDau")
    eRow = .Range("B1000000").End(xlUp).Row
    If eRow > 2 Then
      sArr = .Range("B3:E" & eRow).Value
      For i = 1 To UBound(sArr)
        ik = Dic.Item(sArr(i, 1) & "#" & sArr(i, 3))
        If ik > 0 Then Res(ik, 1) = sArr(i, 4)
      Next i
    End If
  End With
  With Sheets("BrGiua")
    eRow = .Range("B1000000").End(xlUp).Row
    If eRow > 2 Then
      sArr = .Range("B3:E" & eRow).Value
      For i = 1 To UBound(sArr)
        ik = Dic.Item(sArr(i, 1) & "#" & sArr(i, 3))
        If ik > 0 Then Res(ik, 2) = sArr(i, 4)
      Next i
    End If
  End With
  With Sheets("BrCuoi")
    eRow = .Range("B1000000").End(xlUp).Row
    If eRow > 2 Then
      sArr = .Range("B3:E" & eRow).Value
      For i = 1 To UBound(sArr)
        ik = Dic.Item(sArr(i, 1) & "#" & sArr(i, 3))
        If ik > 0 Then Res(ik, 3) = sArr(i, 4)
      Next i
    End If
  End With
  With Sheets("DuLieu")
    .Range("C4:E4").Resize(sRow) = Res
  End With
'****
  ReDim Res(1 To sRow, 1 To 16)
  With Sheets("SoBinh")
    eRow = .Range("B1000000").End(xlUp).Row
    If eRow > 2 Then
      sArr = .Range("B3:E" & eRow).Value
      For i = 1 To UBound(sArr)
        ik = Dic.Item(sArr(i, 1) & "#" & sArr(i, 3))
        If ik > 0 Then Res(ik, sArr(i, 4)) = sArr(i, 2)
      Next i
    End If
  End With
  With Sheets("DuLieu")
    .Range("H4:W4").Resize(sRow) = Res
  End With
'****
  ReDim Res(1 To sRow, 1 To 6)
  With Sheets("DichDu")
    eRow = .Range("B1000000").End(xlUp).Row
    If eRow > 2 Then
      sArr = .Range("B3:G" & eRow).Value
      For i = 1 To UBound(sArr)
        ik = Dic.Item(sArr(i, 1) & "#" & sArr(i, 3))
        If ik > 0 Then
          Res(ik, 2 * sArr(i, 6) - 1) = sArr(i, 4)
          Res(ik, 2 * sArr(i, 6)) = sArr(i, 5)
        End If
      Next i
    End If
  End With
  With Sheets("DuLieu")
    .Range("Y4:AD4").Resize(sRow) = Res
  End With
End Sub
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

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

M
Cho e hỏi về hàm Subtotal ra kết quả bị lỗi
Trả lời
15
Đọc
15K
mrgio
mrgio
Tình nghĩa giang hồ
  • Question
Hỗ trợ phím tắt tương tác màn hình máy in (nút OK) bằng VBA
Trả lời
0
Đọc
13
Tình nghĩa giang hồ
Tình nghĩa giang hồ
LuuAnh980
  • Question
Code lấy dữ liệu từ 3 file con.
2 3 4
Trả lời
73
Đọc
1K
ptm0412
ptm0412
HeSanbi
  • Question
Hàm UDF JoinCells: Tự động gộp chuỗi nhiều ô giữ định dạng trong Excel
    • Thích
    • Yêu thích
2
Trả lời
28
Đọc
9K
tamthat
T
phammaihuong90
Hàm tìm ngày gần nhất đạt được số lượng mục tiêu
Trả lời
5
Đọc
108
Phuocam
Phuocam
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

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

Back
Top Bottom