This bit of code will count how many formulas you have used in a workbook, will display a message box of the count and the workbook name! Enjoy! Sub FormulaCount() Dim ws As Worksheet Dim rCheck As Range Dim lCount As Long On Error Resume Next For Each ws In Worksheets Set rCheck = Nothing […]
If statement Expression
IF Statement Using the IF Statement in the reportal expression builder is much like using the IF statement within excel. if(cellv(col-1,row)>0,cellv(col-1,row)/cellv(col-1,6),0) If the cell value of the column -1 and row is greater than 0, do the calculation else put in 0. This is perfect for a percentage forumla on a table when there […]
Clear or Reset Cells on your Form
This is some simple code to either “Reset” a user entry workbook or to clear cell values before / after use or on command. In this example the “reset” is connected to a button for the user once complete will show a message box “Worksheet Reset”, then to move the cursor to Cell C19 ready […]
Working out SLA Breaches with Excel!
Looking for a simple way to keep track of task and dates? This will help you keep track of SLA’s for a given task, log or project, when handy when dealing with a selection of items. We will guess that the threshold is 8 working days, we can then get Excel to display if the […]
Show and hide sheets based on user
This is a handy bit of code for a simple show and hide command, using the “Windows Username in a cell” code with this show/hide code is a great feature for any workbook used by a teams or departments that have different jobs within the workbook. This is a private sub and will activate on change […]
Dynamically Change Pivot Table Source Data
Dynamically Change Pivot Table Source Data This is a handy bit of code which will save you having to update the source range of a pivot table each time new data is added to the table. In this example I have a data sheet called “Data”. Each month new row’s are added to the table. […]
Extract Month from a date
Extract Month from a date There are a couple of ways to extract just the month from a date in excel. Here are the 2 major ways it can be done. =Month() =MONTH(A2) – returns the month of a date in cell A2. =MONTH(DATE(2015,4,15)) – returns 4 corresponding to April. =MONTH(“15-Apr-2015”) – obviously, returns number […]
Developer Tab and VBA Starting Point
Developer and VBA Starting Point Firstly you will want to add in the Developer tab to your excel ribbon. File > Excel options > Customise Ribbon Tick the Developer Option. Adding a button to a page Many of the VBA scripts we use here start with a button, here is how to add the […]
MS Access – Convert SQL > VBA
SQL Query Into to VBA Language – The Converter This process will enable to you build a SQL query to VBA converter. As VBA cant work with a direct paste of SQL into the VBA scripts this convert will add it line by line with the correct quotation marks and joins. Steps to produce – […]
Excel Conditional Formatting
Conditional Formatting – The basics Quick start! By applying conditional formatting to your data, you can quickly identify variances in a range of values with a quick glance. This is showing 3 items by number over a year. Red for the low scores, Green for the top scores. How To! Select the data you want […]