anh nối 2 sự kiện này lại với nhau giúp e với nhé!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TenCV, Dulieu, Ketqua, KQCV, i As Long, K As Long, H As Long
Dim Ivl As Long, Inc As Long, Imay As Long
Dim VL As String, NC As String, May As String, LaMa As Long
Dim Ma As String, Mahieu As String
Dim iTen_CV As Long
VL = "V" & ChrW$(7853) & "t li" & ChrW$(7879) & "u" ' vat lieu : De tra cuu ben CSDL CM
NC = "Nh" & ChrW$(226) & "n c" & ChrW$(244) & "ng" ' Nhan cong : De tra cuu ben CSDL DM
May = "M" & ChrW$(225) & "y" ' May : De tra cuu ben CSDL DM
On Error Resume Next
If Not Intersect(Target, [B7:B10000]) Is Nothing Then
If Target.Count = 1 Then
Mahieu = Sheets("XuatDL").Range("B" & Target.Row) ' MAHIEU o day la MHCV trong cot B ma ta nhap vao
With Sheets("CSDL TenCV")
TenCV = .Range("A5", .Range("B65535").End(3)).Resize(, 3) ' DL TenCV o B5 den cot D... trong sheet CSDL TenCV
End With
ReDim KQCV(1 To UBound(TenCV), 1 To 3) ' Khai bao mang KQCV la mang 1 dong 3 cot
With Sheets("CSDL DM")
Dulieu = .Range("B5", .Range("B65535").End(3)).Resize(, 6) ' DL dinh muc tu o B5 den G...
End With
ReDim Ketqua(1 To UBound(Dulieu), 1 To 6) ' Mang KETQUA co so dong cua DULIEU va So COT = 6
For i = 1 To UBound(Dulieu)
Ma = Dulieu(i, 1) ' Ma = Ma hieu cong viec o cot 1 cua Mang DuLieu trong CSDL DINH MUC
If Ma = Mahieu Then
For iTen_CV = 1 To UBound(TenCV)
If TenCV(iTen_CV, 1) = Mahieu Then
'KQCV(1, 1) = "=VLOOKUP(RC[-2],'CSDL tenCV'!R5C2:R1800C4,2,0)" ' Tim ten CV trong CSDL tenCV
'KQCV(1, 2) = "=VLOOKUP(RC[-3],'CSDL tenCV'!R5C2:R1800C4,3,0)" ' Tim DVT cua cong viec trong CSDL tenCV
'KQCV(1, 3) = 1 ' Gan Khoi luong = 1
KQCV(1, 1) = TenCV(iTen_CV, 2)
KQCV(1, 2) = TenCV(iTen_CV, 3)
KQCV(1, 3) = 1
Exit For
End If
Next iTen_CV
K = K + 1 ' Bien K la bien chay trong mang KET QUA
If K >= 1 Then ' Neu K > 1 thi gan gia tri cho mang KET QUA tu mang Du Lieu
'J = I - 1 ' Phai giam bien chay I xuong 1 don vi vi thieu 1 dong
If Dulieu(i, 4) = VL Then ' Neu cot so 4 cua sheet CSDL DM la VAT LIEU thi lam ....
Ivl = Ivl + 1
If Ivl = 1 Then
Ketqua(K, 2) = ChrW(97) & ".) " & VL ' Danh chu a). Vat Lieu
K = K + 1
End If
Ketqua(K, 1) = Dulieu(i, 2): Ketqua(K, 2) = Dulieu(i, 5): Ketqua(K, 5) = Dulieu(i, 3): Ketqua(K, 3) = Dulieu(i, 6)
End If
If Dulieu(i, 4) = NC Then
Inc = Inc + 1
If Inc = 1 Then
Ketqua(K, 2) = ChrW(98) & ".) " & NC ' Danh chu b). Nhan Cong
K = K + 1
End If
Ketqua(K, 1) = Dulieu(i, 2): Ketqua(K, 2) = Dulieu(i, 5): Ketqua(K, 5) = Dulieu(i, 3): Ketqua(K, 3) = Dulieu(i, 6)
End If
If Dulieu(i, 4) = May Then
Imay = Imay + 1
If Imay = 1 Then
Ketqua(K, 2) = ChrW(99) & ".) " & May ' Danh chu c). May
K = K + 1
End If
Ketqua(K, 1) = Dulieu(i, 2): Ketqua(K, 2) = Dulieu(i, 5): Ketqua(K, 5) = Dulieu(i, 3): Ketqua(K, 3) = Dulieu(i, 6)
End If
'I = I + 1
End If ' Ket thuc khi K = 1 hoac la K > 1
End If ' Ket thuc khi MA = MA HIEU
Next i
If K Then
Target.Offset(, 2).Resize(1, 3) = KQCV
Target.Offset(1, 1).Resize(K, 5) = Ketqua
With Range("A" & Target.Row & ":J" & Target.Row).Resize(K + 1)
.Borders.LineStyle = 1
.Borders(xlInsideHorizontal).Weight = xlHairline
End With
With Range("A" & Target.Row & ":E" & Target.Row).Resize(K + 1)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Range("D" & Target.Row).Resize(K + 1)
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Else
MsgBox "Khong tim thay"
End If
End If
End If
End Sub
............................................................................................
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cll As Range
If Intersect(Target, [A4:A65536]) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each Cll In Intersect(Target, [A4:A65536]).Cells
If Not Cll.HasFormula And Cll.Value <> "" Then Cll.Value = UCase(Cll.Value)
Next
Application.EnableEvents = True
End Sub
