Thứ Sáu, 8 tháng 1, 2021

Sub tạo sổ nhật ký chung

Sub SCT_NHATKYCHUNG()
On Error Resume Next
Dim MyArrayINDEX()
Dim MyArrayLOCIN()
Dim MyArraySAPXEP()
SCTPS = 0
PSNO = 0
PSCO = 0
For i = HSR_DNKC + 1 To HSR_CNKC - 1
'If Month(NKC.Range("C" & i).Value) >= HSTHANGDAU And Month(NKC.Range("C" & i).Value) <= HSTHANGCUOI _
'And NKC.Range("j" & i).Value <> 0 Then
If Month(NKC.Range("C" & i).Value) - HSTHANGDAU >= 0 And Month(NKC.Range("C" & i).Value) - HSTHANGCUOI <= 0 _
And NKC.Range("j" & i).Value <> 0 Then
SCTPS = SCTPS + 1
ReDim Preserve MyArrayINDEX(SCTPS - 1)
ReDim Preserve MyArrayLOCIN(SCTPS - 1)
ReDim Preserve MyArraySAPXEP(SCTPS - 1)
MyArrayINDEX(SCTPS - 1) = NKC.Range("C" & i).Row - (HSR_DNKC - 1)
MyArrayLOCIN(SCTPS - 1) = 1
MyArraySAPXEP(SCTPS - 1) = Right("0" & Year(NKC.Range("C" & i).Value), 2) & "-" & Right("0" & Month(NKC.Range("C" & i).Value), 2) & "-" & Right("0" & Day(NKC.Range("C" & i).Value), 2) & "A" & NKC.Range("D" & i).Value & Right("0000" & i, 5) & "A"
PSNO = PSNO + NKC.Range("J" & i).Value
PSCO = PSCO + NKC.Range("J" & i).Value
End If
Next
If SCTPS = 0 Then
MsgBox "KHONG CO CHUNG TU PHAT SINH"
GoTo thoat
End If
If FLG_NKC = 1 Then
Dim SH1 As Worksheet
Set SH1 = Sheets("SONHATKYCHUNG")
SH1.Select
'BO TRICH DU LIEU IN
SH1.Range("I" & HSDONGDAU - 1 & ":" & "I" & HSDONGCUOI).Select
SH1.Range("I" & HSDONGDAU - 1 & ":" & "I" & HSDONGCUOI).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
DK.Range("DK1_DK"), Unique:=False
SH1.ShowAllData
SH1.Range("A" & HSDONGDAU & ":" & "IV" & HSDONGCUOI).ClearContents
SH1.Range("IV" & HSDONGDAU + 1 & ":" & "IV" & HSDONGDAU + 1 + SCTPS - 1) _ = WorksheetFunction.Transpose(MyArrayINDEX)
SH1.Range("IT" & HSDONGDAU + 1 & ":" & "IT" & HSDONGDAU + 1 + SCTPS - 1) _ = WorksheetFunction.Transpose(MyArraySAPXEP)
SH1.Range("I" & HSDONGDAU + 1 & ":" & "I" & HSDONGDAU + 1 + SCTPS - 1) _ = WorksheetFunction.Transpose(MyArrayLOCIN)
'LAY DU LIEU
SH1.Range("B" & HSDONGDAU + 1 & ":" & "b" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = "=INDEX(VUNGDULIEUNKC,RC[254],4)"
If FLG_NKCHD = 1 Then
SH1.Range("C" & HSDONGDAU + 1 & ":" & "c" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = "=INDEX(VUNGDULIEUNKC,RC[253],116)"
Else
SH1.Range("C" & HSDONGDAU + 1 & ":" & "c" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = "=INDEX(VUNGDULIEUNKC,RC[253],2)"
End If
'OPT_NTK
SH1.Range("D" & HSDONGDAU + 1 & ":" & "d" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = "=INDEX(VUNGDULIEUNKC,RC[252],5)"
SH1.Range("E" & HSDONGDAU + 1 & ":" & "e" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = "=INDEX(VUNGDULIEUNKC,RC[251],6)"
SH1.Range("F" & HSDONGDAU + 1 & ":" & "f" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = "=INDEX(VUNGDULIEUNKC,RC[250],8)"
SH1.Range("G" & HSDONGDAU + 1 & ":" & "g" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = "=INDEX(VUNGDULIEUNKC,RC[249],10)"
SH1.Range("H" & HSDONGDAU + 1 & ":" & "h" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = "=INDEX(VUNGDULIEUNKC,RC[248],10)"
SH1.Range("L" & HSDONGDAU + 1 & ":" & "l" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = "=RC[-10] & "" - "" & RC[-7]" '"=RIGHT(YEAR(RC[-9]),2) & "" - "" & RIGHT(""0"" & MONTH(RC[-9]),2) & "" - "" & RIGHT(""0"" & DAY(RC[-9]),2) & RC[-10] & "" - "" & RC[-7]" SH1.Range("M" & HSDONGDAU + 1 & ":" & "m" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = "=RC[-11] & "" - "" & RC[-7]" '"=RIGHT(YEAR(RC[-10]),2) & "" - "" & RIGHT(""0"" & MONTH(RC[-10]),2) & "" - "" & RIGHT(""0"" & DAY(RC[-10]),2) & RC[-11] & "" - "" & RC[-7]"
If F_SNKC.CHK_TP.Value = True Then
SH1.Range("A" & HSDONGDAU + 1 & ":" & "a" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = "=INDEX(VUNGDULIEUNKC,RC[255],163)"
SH1.Range("a" & HSDONGDAU - 1).Value = "Khaùch haøng"
SH1.Range("g" & HSDONGDAU - 1).Value = "Soá Löôïng"
SH1.Range("h" & HSDONGDAU - 1).Value = "Soá Tieàn"
SH1.Range("g" & HSDONGDAU - 2).Value = "---"
SH1.Range("o" & HSDONGDAU + 1 & ":" & "o" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(INDEX(VUNGDULIEUNKC,RC[241],4),VUNG_CTINR,1,0)),""Chua In"",""In Roi"")"
SH1.Range("g" & HSDONGDAU + 1 & ":" & "g" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = _
"=INDEX(VUNGDULIEUNKC,RC[249],174)"
'CHK_CI
If F_SNKC.CHK_CI.Value = True Then
SH1.Range("i46").Value = "Chua In"
SH1.Range("g46").Formula = "=SUMIF(O" & HSDONGDAU + 1 & ":O" & HSDONGCUOI & ",I46,G" & HSDONGDAU + 1 & ":G" & HSDONGCUOI & ")"
SH1.Range("h46").Formula = "=SUMIF(O" & HSDONGDAU + 1 & ":O" & HSDONGCUOI & ",I46,h" & HSDONGDAU + 1 & ":h" & HSDONGCUOI & ")"
giatrichuainG = SH1.Range("g46").Value
giatrichuainH = SH1.Range("h46").Value
End If
Else
SH1.Range("A" & HSDONGDAU + 1).FormulaR1C1 = "=INDEX(VUNGDULIEUNKC,RC[255],3)"
If F_SNKC.OPT_NTK.Value = True _
Or F_SNKC.OPT_CTK = True Then
SH1.Range("A" & HSDONGDAU + 1).FormulaR1C1 = "=""- "" & INDEX(VUNGDULIEUNKC,RC[255],150)"
End If
End If
SH1.Range("N" & HSDONGDAU + 1 & ":" & "N" & HSDONGDAU + 1 + SCTPS - 1).FormulaR1C1 = "=rc[-12]&rc[-9]&rc[-8]"
If F_SNKC.CHK_CI.Value = True Then
SH1.Range("G46").Value = giatrichuainG
SH1.Range("H46").Value = giatrichuainH
SH1.Range("G" & HSDONGCUOI + 1).Value = giatrichuainG
SH1.Range("H" & HSDONGCUOI + 1).Value = giatrichuainH
Else
SH1.Range("G46").Value = PSNO
SH1.Range("H46").Value = PSCO
SH1.Range("G" & HSDONGCUOI + 1).Value = PSNO
SH1.Range("H" & HSDONGCUOI + 1).Value = PSCO
End If
SH1.Range("I" & HSDONGDAU + 1 & ":" & "I" & HSDONGDAU + 1 + SCTPS - 1).Value = 1 '''''''''''''''''''''''''''''
SH1.Range("A" & HSDONGDAU + 1 & ":" & "H" & HSDONGDAU + 1).Copy
SH1.Range("A" & HSDONGDAU + 1 & ":" & "H" & HSDONGDAU + 1 + SCTPS - 1).Select
ActiveSheet.Paste
SH1.Range("L" & HSDONGDAU + 1 & ":" & "M" & HSDONGDAU + 1).Copy
SH1.Range("L" & HSDONGDAU + 1 & ":" & "M" & HSDONGDAU + 1 + SCTPS - 1).Select
ActiveSheet.Paste
' SH1.Range("A" & HSDONGDAU + 1 & ":" & "H" & HSDONGDAU + 1 + SCTPS - 1).FillDown
'SAP XEP
SH1.Range("A" & HSDONGDAU + 1 & ":" & "IV" & HSDONGDAU + 1 + SCTPS - 1).Select
sudungham = _ f_sx1c_A(SH1.Range("A" & HSDONGDAU + 1 & ":" & "IV" & HSDONGDAU + 1 + SCTPS - 1), _ SH1.Range("IT" & HSDONGDAU + 1 & ":" & "IT" & HSDONGDAU + 1 + SCTPS - 1))
SH1.Range("A" & HSDONGDAU + 1 & ":" & "IV" & HSDONGDAU + 1 + SCTPS - 1).Select
Selection.Copy
SH1.Range("A" & HSDONGDAU + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'SH1.Range("g46").Formula = "=SUMIF(O" & HSDONGDAU + 1 & ":O" & HSDONGCUOI
ActiveWorkbook.Names("VUNGIN").Delete
'VUNG IN
ActiveWorkbook.Names.Add Name:="VUNGIN", RefersToR1C1:= _
SH1.Range("A40" & ":" & "h" & HSDONGCUOI + 6)
ActiveSheet.PageSetup.PrintArea = "VUNGIN"
thoat:
'TRICH LOC
If F_SNKC.OPT_NTK.Value = True _
Or F_SNKC.OPT_CTK = True Then
If F_SNKC.CHK_TP.Value = True Then
Else
SH1.Range("a49").Value = "C.töø Keøm Theo"
End If
End If
SH1.Range("i" & HSDONGDAU - 1 & ":" & "IV" & HSDONGCUOI).Select
Selection.AutoFilter Field:=1, Criteria1:="1"
If HSTHANGDAU = HSTHANGCUOI Then
SH1.Range("d45").Value = "THAÙNG " & Right("0" & HSTHANGDAU, 2) & " NAÊM " & NT.Range("ONLV_NT").Value
Else
If HSTHANGDAU = 1 And HSTHANGCUOI = 12 Then
SH1.Range("d45").Value = "NAÊM " & NT.Range("ONLV_NT").Value
Else
If HSTHANGDAU < HSTHANGCUOI Then
SH1.Range("d45").Value = "TÖØ THAÙNG " & Right("0" & HSTHANGDAU, 2) & " ÑEÁN THAÙNG " & Right("0" & HSTHANGCUOI, 2) & " NAÊM " & NT.Range("ONLV_NT").Value
End If
End If
End If
End If
End Sub
Latest
Next Post

0 nhận xét: