Tutorials For Excel everyday with EVBA.info part 16[Free PDF ebook] - EBOOK VBA EXCEL

EBOOK VBA EXCEL

EVBA.info ( Ebook VBA Excel ) Welcome EVBA.info - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Free Courses, Living Skills ...

Monday, November 11, 2019

Tutorials For Excel everyday with EVBA.info part 16[Free PDF ebook]

Tutorials For Excel everyday with EVBA.info part 16[Free PDF ebook]



EVBA’s Tutorials For Excel: Formatting Dates as Past or Future

Here’s how you can mark dates in the past and/or in the future, based on some time interval such as less than or more than 30 days from today’s date. For example, in the first picture, today’s date is held in cell E2 using the TODAY function. Conditional Formatting in column C colors dates more than 30 days ago as blue, and more than 30 days from today as yellow.




To do this, select the cells of interest, in this case range C5:C15. Then, press Alt+O+D on your keyboard to show the Conditional Formatting dialog box.



If you are using Excel version 2003 or before, the Conditional Formatting dialog box looks like the next picture, where you can enter the two formula rules for past and future time.
The formula for dates more than 30 days ago is:
=AND(LEN(C5)>0,C5<$E$2-30)
The formula for dates more than 30 days in the future is:
=AND(LEN(C5)>0,C5>$E$2+30)
Two points about these formulas:
• The LEN function keeps empty cells uncolored, which Excel would interpret as a date.
• Cell E2 houses today's date and is an efficient reference for the Conditional Formatting formulas, rather than using the TODAY function in all the formatted cells' formula rules. Also, if you change your base time element, for example from today to yesterday, you only need to change the formula in cell E2 from =TODAY() to =TODAY()-1.



If you are using Excel version 2007, 2010, or 2013, you'll first see the Rules Manager dialog box. Click the New Rule button as indicated.



Go ahead and enter the same formulas as shown above for Excel versions up to 2003:
The formula for dates more than 30 days ago is:
=AND(LEN(C5)>0,C5<$E$2-30)
The formula for dates more than 30 days in the future is:
=AND(LEN(C5)>0,C5>$E$2+30)



But wait! What if you just want to use one color? Fair question!
You might not care about seeing a visual difference between 30+ days ago and 30+ days from now, but you would want to see the visual indication of dates that are either 30+ days past or present.



For this, you only need one color, which means means you only need one formula rule:
=AND(LEN(C5)>0,OR(C5<$E$2-30,C5>$E$2+30))
In Excel versions 2003 or before, the Conditional Formatting dialog box looks like this:



In Excel versions 2007 or after, the Conditional Formatting dialog box looks like this:

Tom’s Tutorials For Excel: Using VLOOKUP for Reverse Lookups
I previously posted this example of a reverse lookup, using the INDEX and MATCH functions.
The VLOOKUP function is commonly used with a table of data where the item being looked up is in a column to the left of the column holding the corresponding item you want to return, for example:



When you need to return a value from a column to the left of where your criteria is listed in the table, the VLOOKUP function can be used with an array constant (explained here), for example:
=VLOOKUP(I2,IF({1,0,0},C2:C13,A2:A13),3,0)



If your table has many columns between the lookup column and the corresponding item’s column, this formula from fellow Excel MVP Bob Umlas uses the more efficient CHOOSE function:
=VLOOKUP(I2,CHOOSE({1,2},G2:G13,A2:A13),2,0)

Here’s some info about why the formula with the array constant works.
ArrayConstant
Tom’s Tutorials For Excel: Listing Subfolders
I previously posted this example to list files kept in a parent folder.
Today’s example shows how you can list the subfolder names belonging to a parent folder.



Here’s the macro that does this:

Sub ListSubfolders()
'Declare and define variables.
Dim SourcePath As String, NextRow As Long
Dim objFSO As Object, objFolder As Object, objSubfolder As Object
SourcePath = "C:\Drivers\"
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set the folder object associated with your directory path.
'Halt the macro if the directory folder does not exist.
On Error Resume Next
Set objFolder = objFSO.GetFolder(SourcePath)
If Err.Number <> 0 Then
Err.Clear
MsgBox "This parent folder does not exist:" & vbCrLf & _
SourcePath & vbCrLf & _
"Cannot continue.", 16, "No such animal."
Exit Sub
End If
'Define the NextRow variable as row 2.
'Row 1 will contain a header label.
NextRow = 2
'Turn off screen updating.
Application.ScreenUpdating = False
'The subfolder names will be listed in column A.
'Clear column A to start with a clean column.
Columns(1).Clear
'Place a header label in cell A1.
Range("A1").Value = "Subfolders in " & SourcePath
'Loop through the subfolders in the primary folder
'to list its subfolder names.
For Each objSubfolder In objFolder.SubFolders
Cells(NextRow, 1).Value = objSubfolder.Name
NextRow = NextRow + 1
Next objSubfolder
'Release the Objects variables from memory.
Set objFSO = Nothing
Set objFolder = Nothing
Set objSubfolder = Nothing
'For easier readability, autofit column A.
Columns(1).AutoFit
'Turn ScreenUpdating back on.
Application.ScreenUpdating = True
End Sub
Tom’s Tutorials For Excel: Returning text with your formula’s numeric results
I previously posted this example with text, and this example with dates, to show how a custom format can display text in cells without affecting the actual underlying values of those cells.
Today’s example shows how text can be displayed in a cell that returns a numeric result, while keeping that cell’s returned numeric value as a true, unaffected number. Recall, formatting a cell only changes the appearance of the cell’s contents. The cell’s actual value remains unchanged, be it a number, text, or a boolean (True or False) value.
The first picture is a Before and After comparison with a custom format in cell F7.



To make this happen, select your cell and press the Alt+O+E keys.



The Format Cells dialog box will appear.
• Select the Number tab.
• In the Category list, select Custom.
• In the Type field, enter "Total Income: " #,### and click OK.

Tom’s Tutorials For Excel: Listing Your Excel Files From a Folder
Here’s a macro to list the names of all your Excel workbooks from a specified folder into column A of your worksheet.
In the first picture, you see several different types of files in the selected folder. The following macro will list only the Excel workbook names, as shown in the second picture.
To modify the macro to list, say, only the PowerPoint files, change ".xls" to ".ppt". If you want to list all files of all types, simply delete or comment out the lines
If InStr(objFile.Name, ".xls") > 0 Then
and the End If statement three lines below that.





Here’s the macro, which works in all version of Excel.

Sub ListExcelFiles()
'Declare and define variables.
Dim SourcePath As String, NextRow As Long
Dim objFSO As Object, objFolder As Object, objFile As Object
SourcePath = "C:\Your\File\Path\"
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set the folder object associated with your directory path.
'Halt the macro if the directory folder does not exist.
On Error Resume Next
Set objFolder = objFSO.GetFolder(SourcePath)
If Err.Number <> 0 Then
Err.Clear
MsgBox "This path does not exist:" & vbCrLf & _
SourcePath & vbCrLf & _
"Cannot continue.", 16, "No such animal."
Exit Sub
End If
'Define the NextRow variable as row 2.
'Row 1 will contain a header label.
NextRow = 2
'The workbook names will be listed in column A.
'Clear column A to start with a clean column.
Columns(1).Clear
'Place a header label in cell A1.
Range("A1").Value = "Excel files in the path " & SourcePath
'Loop through the Files in the source directory folder
'to list only the Excel file names.
For Each objFile In objFolder.Files
If InStr(objFile.Name, ".xls") > 0 Then
Cells(NextRow, 1).Value = objFile.Name
NextRow = NextRow + 1
End If
Next objFile
'Release the Objects variables from memory.
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
'For easier readability, autofit column A.
Columns(1).AutoFit
'Optional, sort the list.
Range("A1").CurrentRegion.Sort _
Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End Sub
Tom’s Tutorials For Excel: Avoiding the #DIV/O! (Cannot Divide by Zero) Error
Some errors are unavoidable, depending on the nature of your project. You probably know about the ISERROR function, but ordinarily you shouldn’t use a catch-all error function like that. Some errors, such as #REF!#N/A, or #NAME? are invaluable for identifying other kinds of errors which, if undetected, will destroy the integrity of your workbook’s calculations without you realizing it.
The simple formula =B4/C4 in the picture on the left returns that familiar (and ugly-looking) #DIV/0! error when a zero or empty cell is the denominator in a division formula.
The alternative formula =IF(C4,B4/C4,"") in the picture on the right avoids the visible #DIV/0! error, without trying to catch every error under the sun. You can replace the null string with text if you prefer, such as “No prior years”, but the idea is to have a pleasant-looking worksheet for users who’d rather see something meaningful in a cell, or nothing at all, instead of the #DIV/0! error.
Note that this =IF(C4,B4/C4,"") syntax will work in all versions of Excel. Starting with version 2007, you can also use the =IFERROR function, for example =IFERROR(B4/C4,"").

Tom’s Tutorials For Excel: Highlighting the Current and Prior Selected Cells.
Today, here is how you can highlight not only the current cell, but the cell you’d selected before you selected your current cell. To make it easy to distinguish between the two cells, the currently selected cell is colored cyan, and the prior selected cell is colored magenta.
Take a look at the next two pictures. In the first picture, cell C5 is the active (currently selected) cell. You can tell by its cyan color, and also you can see its address in the address bar. Before this picture was taken, cell H12 had been selected, evidenced by its magenta color.



This next picture shows that the currently selected cell is L18, colored cyan. Notice that now, cell C5, which was selected before as seen in the first picture, is colored magenta.



Here is the code that accomplishes this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = 0
Static PriorCell As Range
If Not PriorCell Is Nothing Then PriorCell.Interior.Color = vbMagenta
Target.Interior.Color = vbCyan
Set PriorCell = Target
End Sub
Here are the steps to install the code:

Step 1


Step 2
Copy the above code and paste it into the worksheet module where shown.



Step 3
Press Alt+Q on your keyboard to return to your worksheet.

Tom’s Tutorials For Excel: Toggling a formula for relative or absolute references
I previously posted this macro example for converting a bunch of formula-containing cells all at once, from relative to absolute references.
Alternatively, you can quickly toggle a single formula’s row and/or column references for relative and absolute with an easy keyboard shortcut. There’s no VBA or programming involved.
Just double-click the cell, or select the cell and hit the F2 key to edit the cell…


…then hit the F4 key to toggle for relative all; absolute row; absolute column; or absolute all.


In this example, cell D15 is selected, which is averaging a few ranges.


With cell D15 selected, hit the F2 key, or double-click the cell to go into Edit mode.


I selected all the range arguments between the parentheses. You don’t need to select all range arguments, just the one(s) you want to toggle.


With the original formula having all relative references, hitting the F4 key the first time changes all selected row and column references to absolute. Notice that the changes are concurrently reflected in the formula bar.


The second time you hit F4, the column references are relative and the row references are absolute.


The third time you hit F4, the column references are absolute and the row references are relative.


The fourth time you hit F4, you have completed the toggle cycle and started a new one, right back where you started in this example, with all range references being relative as they originally were.
Tom’s Tutorials For Excel: One formula returns value of the same cell on multiple worksheets
Here’s how one formula can return the value of the same cell address from multiple worksheets. In the picture, you have an expense workbook with five worksheets. Four of the worksheets (named Sheet2, Sheet3, Sheet4, and Sheet5) are set up the same way, with their regional total expense amount in cell B12.



On the Summary sheet of your workbook is “B12” (without the quotes) in some cell, for this example cell E1 as seen in the following picture. Now, using the INDIRECT function to refer to that cell value in E1, and the ROW function to help refer to each of the four source sheet names, you can enter this formula as seen in cell B3…
=INDIRECT("'Sheet"&ROW()-1&"'!"&$E$1)
…and copy it down as needed. This returns the value of cell B12 in each worksheet.



The trick is simply to plan ahead when you design your workbook to allow for a single formula to do the work of many formulas. And the beauty of this system is, if you change the location of the Total cell on the regional worksheets, such as if another expense item is added, simply modify cell E1 on the Summary sheet for that new Total cell address.
Tom’s Tutorials For Excel: Entering New Lines and Bullets in a Cell
Here is how you can enter text on its own line in a single cell. In the picture, some food items have been entered into cell A2. As you can see in the pictures that follow, the key is to press Alt+Enter at the point where you want to start a new line.









Finally, if you want to precede your new line’s text with a bullet character, type the key sequence Alt+0149 from the “number pad” section of your keyboard.

📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment