Useful code

TIỆN ÍCH MIỄN PHÍ


UNIQUE FUNCTION CAN BE USED IN DATA TABLE

Download

 

INDEX and MATCH with multiple criteria

INDEX(return_range, MATCH(1, INDEX((range1=criteria1) * (range2=criteria2) , 0, 1), 0))

INDEX(return_range, MATCH(1, INDEX((range1=criteria1) * (range2=criteria2) *(range3=criteria3) , 0, 1), 0))

 

Code tự động refresh query

Sub Auto_Open()

Dim t As Double
t = Now() + TimeSerial(0, 5, 0)
'TimeSerial(Hour,Minute,Second)

Application.OnTime t, "Query"
Application.OnTime t, "Auto_Open"
                          
End Sub

Private Sub Query()
Application.Run ("'C:\miniMis\miniSql.xlam'!Query_Workbook")
End Sub

 

Code tự động tắt tính toán sheet +

Sub Auto_Open()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If Left(Trim(ws.Name), 1) = "+" Then ws.EnableCalculation = False
Next ws
Set ws = Nothing

End Sub
 

Sub to Refresh

FullCalculate

=AND(MOD(ROW();2)=1;NOT(ISBLANK($A20)))