With the simple Excel macro below, we can make all cells in active Worksheet that contains Error value in it, like #NULL, #Div/0!, #VALUE!, #Ref, #NAME?, #NUM!, And #N/A, will automatically having cell format that different/stand out among all other cells.
In this example, background color of the cell that contains the error value will automatically change color to red, [...] Continue Reading…
Ok, this time I want to talk about how to create a vlookup table array that automatically expanding in accordance with the numbers of available data.
As we know, the basic form of Excel VLOOKUP to look for A1 value in range B1:D15 column no 2 is like this:
=VLOOKUP(A1,B1:D15,2)
With parameters such as the value we looking for, range of table [...] Continue Reading…
Let say I have a bunch of formatted data in my Excel sheet, and in Column A I have a dates. Then I wanna filter all the data base on certain date, for example 1 Jan 2009, and delete all others data before that date.
The algorithm is like this: I’ll create a loop from the first row until the [...] Continue Reading…
A few weeks ago I received this question from someone called LS:
Hi
I want to write a macro that counts the following
- Number of cells with formula
- Number of Rows with formula
- Number of Columns with formula
Please let me know if there is a way of doing it in Excel VBA
To find out the number of cells, rows, or columns [...] Continue Reading…
Auto run macro excel… Do you want to run an excel macros automatically when the file is open or close?
Microsoft Excel provide us the opportunity to run a macro automatically every time we open or close the excel workbook file, using the event Workbook_Open and Workbook_BeforeClose.
In the Microsoft Visual Basic Editor page, in the ThisWorkbook module, we simply create two new sub [...] Continue Reading…
Do while … loop, do until … loop, do … loop while, do … loop until, and while … wend are the format of looping that we can use in Excel VBA.
The main reason why we need to perform looping is to simplified a repeating tasks. Rather then manually calling a block of code over and over again, it’s [...] Continue Reading…
Converting Excel column number into column name, for example 1 into A or 52 into AZ, is needed occasionally, and the following function will do the convertion for us.
Function Number2Char(ByVal vNumber)
Dim iDiv As Double, iMod As Integer
If vNumber < 1 Then Exit Function
iDiv = vNumber
[...] Continue Reading…
Latest Excel Scorecard