Solution The VBA macro code finds all the dependent cells of the active cell (selected cell) and displays their full address in a message box. This macro shows all dependent cells including ones that are outside of current worksheet and workbook. The key to the solution is to use the method “Range.Navigatearrow” to go through…
The below code changes the width and height of any object in Powerpoint to exactly 5 inches without changing the aspect ratio. The aspect ratio is the ratio of height and width. Keeping the same aspect ratio prevents an image from looking stretched. But before you run this code/macro, you first need to select the…
The data table is powerful tool, but it brings a new level of complexity which can result in more ways to break or create wrong results. This can cause wrong calculation output values or the table to not work at all. Below are causes of why your data table does not work, and how to fix…
Click here to download the Automatic Goal Seek / Guess and Test VBA Macro with Excel Input Template. With Goal Seek, Excel can calculate an answer by guessing and testing until it gets to the correct answer. The Goal Seek operation is quite handy when an answer we want cannot be calculated using normal formulas.…
Using the For Each Loop The code below loops through all worksheets in the workbook, and activates each worksheet. The code uses the “for each” loop to loop through the wrosheets contained inside ThisWorkbook. After it is done looping through all the worksheets, it reactivates the original worksheet that was active before running the macro.…
In a prior post (How to unhide or delete name ranges in Excel?), we talked about how to unhide and use the name range interface (Ctrl+F3) to select and delete name ranges. But what if you want to use VBA to delete name ranges quickly or with certain conditions? It is not uncommon to see older worksheets ending…
Sometimes you want to apply the “IFERROR” in Excel to trap errors and avoid “ugly” output, such as below: Let’s say for a report to your manager, you may have cleaner output. Instead of showing “#DIV/0”, you want to show “n/a” as the data point is not available. The best way is to use the…
Click here to download the Linear Interpolation Extrapolation Template with Chart (Free template with full functionality) Wikipedia definition: In mathematics, linear interpolation is a method of curve fitting using linear polynomials. For more info: http://en.wikipedia.org/wiki/Linear_interpolation Introduction to the Excel Template This template allows you to quickly perform a linear interpolation (and extrapolation) between a flexible data set,…
A data table will work even if it is on a separate worksheet as the input cells. If you want a step by step solution, skip to the section below. You can download an example in Excel here: https://excelhelphq.com/wp-content/uploads/2014/08/Datatable-Example.xlsx Strategy The strategy is to have your original excel sheet’s input cells to change only when the…
Click here to download the Three Variable Data Table Example. Introduction The key to making a three-variable data-table (or any higher number of variables, such as 4, 5, etc.) is to use the offset function to populate a set of values into the base calculation. (The data-table’s constraint of only having two variables remain unchanged.) Setting Up…