Quick Reference

Knowledge Sharing

How to calculate SUM in Excel?

How to calculate SUM in Excel? Sum in Excel can be performed with “+” symbol. However, when large data set is present, it is better to go with sum function and you will see the advantages below. Sum can include numbers, cell references, ranges, arrays, and constants. Limited to 255 individual arguments. Sum function is used...

Delete First line of Text file and Import -Excel vba

Delete First line of Text file and Import -Excel vba Select Folder that contains the text files. It reads each text file into an array and then builds a new array (that doesn’t include the first line of each text file) first way (not verified):  Sub SO() Const fileLocation As String = “C:\Users\BloggsJ\Folder\textFile.txt” Dim...

Replace specific line of text file using Excel VBA

Replace specific line of text file using Excel VBA It includes both the first line replacement, and the last 6 characters replacement. Sub GenerateACEQueries() 'Script sourced from 'http://www.excelbanter.com/showthread.php?t=427791 'http://www.ozgrid.com/forum/showthread.php?t=181679 Dim strFileNum As String Dim rCell As Range ‘Declarations for parsing first line in text file (opening date) Dim vSz As Variant, vFilename As Variant...

Range Selection in Excel VBA

Range Selection in Excel VBA Range(“A1:J70,T1:AB70”).Select The ranges are selected separately.   Range(“A1:J70″,”T1:AB70”).Select The range including middle ones are also selected.  

How To Vlookup Values Across Multiple Worksheets?

How To Vlookup Values Across Multiple Worksheets? Vlookup across multiple worksheets can be found using regular formulas. It is easy to do with array formula using array formulas. Regular Formula: =IFERROR(VLOOKUP($A2,Sheet1!$A$2:$B$5,2,FALSE),IFERROR(VLOOKUP($A2,Sheet2!$A$2:$B$5,2,FALSE),VLOOKUP($A2,Sheet3!$A$2:$B$5,2,FALSE))) Array Formula: =VLOOKUP(A2,INDIRECT(“‘”&INDEX(Sheetlist,MATCH(1,–(COUNTIF(INDIRECT(“‘”&Sheetlist&”‘!$A$2:$B$5″),A2)>0),0))&”‘!$A$2:$B$5”),2,FALSE) In the above formula: A2: is the cell reference which you want to return its relative value; Sheetlist: is the...

Folder Exist Check in Excel VBA

Folder Exist Check in Excel VBA Checks whether a folder exists are not? Set FSO = CreateObject("scripting.filesystemobject") If FSO.FolderExists(FromPath) = False Then MsgBox FromPath & " doesn't exist" Exit Sub End If

Move Files from one folder to another Excel VBA

Move Files from one folder to another Excel VBA Move files from one folder to another folder. If required, you can create a new folder Sub move_data() 'Move test data to folder Dim FSO As Object Dim FromPath As String Dim ToPath As String Dim Fdate As Date Dim FileInFromFolder As Object MkDir "D:\TEST\"...

Wait/Delay in Excel VBA

Wait/Delay in Excel VBA Delaying the process for few seconds to hours in Excel VBA newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 10 waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime   If Application.Wait(Now + TimeValue(“0:00:10”)) Then MsgBox “Time expired” End If https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-wait-method-excel

Published On

December 2018
M T W T F S S
« Nov    
 12
3456789
10111213141516
17181920212223
24252627282930
31