This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table => | |
let | |
DayCount = Duration.Days(Duration.From(EndDate - StartDate)), | |
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), | |
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), | |
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), | |
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}), | |
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text), | |
InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])), | |
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub CheckIfFileOpen() | |
Dim fileName As String | |
fileName = "C:\Users\marks\Documents\......." 'Tên file kèm đường dẫn | |
'Gọi function kiểm tra file đóng hay mở | |
If IsFileOpen(fileName) = False Then | |
'Câu lệnh thực hiện khi file đang đóng | |
Else | |
'File đang mở hoặc lỗi khác |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Function IsFileOpen(fileName As String) 'Function kiểm tra file đang mở hay đóng | |
Dim fileNum As Integer | |
Dim errNum As Integer | |
'Bỏ qua các lỗi có thể gặp | |
On Error Resume Next | |
fileNum = FreeFile() | |
'Thực hiện việc mở / đóng file xem có lỗi không |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub AnHang() | |
Dim TuDong As Long, DenDong As Long | |
TuDong = Sheets("Sheet1").Range("B1").Value | |
DenDong = Sheets("Sheet1").Range("B2").Value | |
'Bo an tu dong 1 den 10000 | |
Sheets("Sheet1").Range("A1:A10000").EntireRow.Hidden = False | |
'An dong duoc chon | |
Sheets("Sheet1").Range("A" & TuDong & ":A" & DenDong).EntireRow.Hidden = True |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub Update_BC() | |
'1. Xoa du lieu cu | |
Sheets("ChiTiet").Range("A7:F1000").Clear | |
'2. Cap nhat ket qua voi advanced Filter | |
Sheets("ChiPhi").Range("A2:K722").AdvancedFilter _ | |
Action:=xlFilterCopy, _ | |
CriteriaRange:=Range("I6:K7"), _ | |
CopyToRange:=Range("A6:F6"), _ | |
Unique:=False | |
'3. Tim dong cuoi |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub SapXep() | |
'Bien dong cuoi | |
Dim lr As Long | |
lr = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row | |
'Dieu kien chi sap xep khi bang tinh co du lieu | |
If lr > 1 Then 'Dong tieu de la dong 1 | |
'Xoa cac noi dung sap xep truoc do | |
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear | |
'Sap xep |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub GopSheet_XoaTrung() | |
Dim x As Integer | |
Dim lr as long | |
Dim ws As Worksheet | |
On Error GoTo ErrHandler | |
Application.ScreenUpdating = False | |
'Gộp sheet | |
x = 1 | |
For Each ws In ThisWorkbook.Sheets |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub ChuyenFontUnicode() | |
Dim FontRange As Range, FontName As String, FontSize As String | |
FontName = "Time New Roman" 'Tuy chon Font Unicode | |
Application.ScreenUpdating = False | |
On Error Resume Next | |
For Each FontRange In ActiveSheet.UsedRange | |
With FontRange | |
If UCase(Left(.Font.Name, 3)) = ".VN" Then | |
.Value = TCVN3toUNICODE(.Text) | |
.Font.Name = FontName |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Public Function VniToUni(str$) As String | |
Dim VNI$, UNI$, i&, sUni$, arrUNI() As String | |
VNI = "aù,aø,aû,aõ,aï,aâ,aê,aá,aà,aå,aã,aä,aé,aè,aú,aü,aë,AÙ,AØ,AÛ,AÕ,AÏ,AÂ,AÊ,AÁ,AÀ,AÅ,AÃ,AÄ,AÉ,AÈ,AÚ,AÜ,AË,eù,eø,eû,eõ,eï,eâ,eá,eà,eå,eã,eä,EÙ,EØ,EÛ,EÕ,EÏ,EÂ,EÁ,EÀ,EÅ,EÃ,EÄ,í ,ì ,æ ,ó ,ò ,Í ,Ì ,Æ ,Ó ,Ò ,où,oø,oû,oõ,oï,oâ,ô,oá,oà,oå,oã,oä,ôù,ôø,ôû,ôõ,ôï,OÙ,OØ,OÛ,OÕ,OÏ,OÂ,Ô ,OÁ,OÀ,OÅ,OÃ,OÄ,ÔÙ,ÔØ,ÔÛ,ÔÕ,ÔÏ,uù,uø,uû,uõ,uï,ö ,öù,öø,öû,öõ,öï,UÙ,UØ,UÛ,UÕ,UÏ,Ö ,ÖÙ,ÖØ,ÖÛ,ÖÕ,ÖÏ,yù,yø,yû,yõ,î ,YÙ,YØ,YÛ,YÕ,Î ,ñ ,Ñ " | |
UNI = "E1,E0,1EA3,E3,1EA1,E2,103,1EA5,1EA7,1EA9,1EAB,1EAD,1EAF,1EB1,1EB3,1EB5,1EB7,C1,C0,1EA2,C3,1EA0,C2,102,1EA4,1EA6,1EA8,1EAA,1EAC,1EAE,1EB0,1EB2,1EB4,1EB6,E9,E8,1EBB,1EBD,1EB9,EA,1EBF,1EC1,1EC3,1EC5,1EC7,C9,C8,1EBA,1EBC,1EB8,CA,1EBE,1EC0,1EC2,1EC4,1EC6,ED,EC,1EC9,129,1ECB,CD,CC,1EC8,128,1ECA,F3,F2,1ECF,F5,1ECD,F4,1A1,1ED1,1ED3,1ED5,1ED7,1ED9,1EDB,1EDD,1EDF,1EE1,1EE3,D3,D2,1ECE,D5,1ECC,D4,1A0,1ED0,1ED2,1ED4,1ED6,1ED8,1EDA,1EDC,1EDE,1EE0,1EE2,FA,F9,1EE7,169,1EE5,1B0,1EE9,1EEB,1EED,1EEF,1EF1,DA,D9,1EE6,168,1EE |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Function TCVN3toUNICODE(vnstr As String) | |
Dim c As String, i As Integer | |
For i = 1 To Len(vnstr) | |
c = Mid(vnstr, i, 1) | |
Select Case c | |
Case "a": c = ChrW$(97) | |
Case "¸": c = ChrW$(225) | |
Case "µ": c = ChrW$(224) | |
Case "¶": c = ChrW$(7843) | |
Case "·": c = ChrW$(227) |
NewerOlder