Data Table in Excel


Data Tables can store the results of many different scenarios in one table, so that one can analyze them to select which scenario is your best option.  The results are then written into a table form in your Workbook in a location specified by you. Data Tables are written as array formulas, which therefore allows them to perform multiple…

Excel Formula Challenge :


Hope you will post an excellent solution here : Let suppose you have a string like below : A|A|A|B|B|C|A|B|C|D As you can see the characters are being repeat in this string  output should be : A|B|C|D   Keep excelling  Rajan

Copy Paste From PPT to PPT


  Want to copy Shapes from powerpoint to powerpoint through Excel?   you can use this code      Sub CopyPasteFromPowerPointToPowerPoint()          Dim objPPT As Object     Dim objPresent1 As Object     Dim objPresent2 As Object          Set objPPT = CreateObject(“PowerPoint.Application”)          Set objPresent1…

IsArrayFilled


Function IsArrayFilled(InputArray As Variant) As Boolean     ‘================================================     ‘       this function check Array filled or not     ‘                    ParameterInfo     ‘       InputArray   :   Array you want to check     ‘================================================    …

Check Array Dimension


  Function IsArray2Dimensional(InputArray As Variant) As Boolean                ‘================================================     ‘       this function check is Array 2D or not     ‘                    ParameterInfo     ‘       InputArray   :   Array you want…

Sort 2D Array


 Hi All,   You can use this function to Sort 2D Array, either ascending or descending on selected column , See the MTest Macro to test the code ‘============================================================== Option Explicit Enum SortType     xlAscending     xlDescending End Enum Function BubbleSort(InputArray As Variant, Optional SortColumn As Integer = 1, _            …

Check String Case


Hi All, You can use this function to check the case of a string. Function CheckCase(rngDta as Range) As Byte ‘======================================================== ‘How this Works ‘Pass a string argument to check the case of string ‘it would Return 1 in case of UpperCase ‘it would Return 2 in case of ProperCase ‘it would return 3 in case…

Extract Subset of an array


Hi All, As you know array is extremely faster then working on a range, in some scenerio we need to extract a subset of a range,here is a function to get a subset from a multidimension array, instead of using range you can get the subset from an array, see the tested code below this…

VlookupArray


Function VlookupArray(Lookup_value As Variant, Lookup_Array, Column_number As Long) As Variant          Dim blnFlag As Boolean     Dim VarCol     Dim lngIndex  As Long          ‘==============================================================     ‘Created by Rajan on 14-July-2012     ‘ParameterInfo     ‘Lookup_Value   : Value what you find in the first…

Remove Contents From String:


This function use regular expression object to remove unwanted contents from a string, so you can use this to remove Text,Numeric or other special character defined in Pattern of this function Enum WhatToRemove Text Numeric SpecialCharacters End Enum Function RemoveContents(StringToReplace, WhatRemove As WhatToRemove) As String Dim RegEx As Object Set RegEx = CreateObject(“VBScript.RegExp”) With RegEx…

Filter Array : Revised


Here is an faster and easier way to filter 2D Array Syntex to Use : FilterArray InputArray, ResultArray, FilterColumn, FilterValue, FilterType                 ============================================================================== Enum Combination     xlRow     xlColumn End Enum Enum FltType     xlFilterEqualTo     xlFilterLessThen     xlFilterLessThenEqualTo     xlFilterGreaterThen     xlFilterGreaterThenEqualTo     xlFilterDoesNotEqualTo     xlFilterContains    …

Filter 2D Array [Rows and Columns]


Hi, Here is a function to filter 2D Array either by Rows and Columns. Syntex: FilterArray InputArray , ResultArray , 1 , “Product1”,xlFilterDoesNotEqualTo Suppose you have An Array of Sales Data of Multiple Product , but you need to retrieve data only for one product in another array, Use this 🙂 ================================================================================ Enum Combination    …

Hello world!


Welcome to WordPress.com! This is your very first post. Click the Edit link to modify or delete it, or start a new post. If you like, use this post to tell readers why you started this blog and what you plan to do with it. Happy blogging!

Fill Array One D to Two D


Below function used to fill one dimensional array elements to 2D Array based on Given parameters, ================================================================================================================================== Enum Combination     xlRow     xlColumn End Enum Enum FillType     FillRowWise     FillColumnWise End Enum ================================================================================================================================== Function JustifyArray(varArrInput As Variant, VarArrResult As Variant, TransformIn As Combination, HowMany As Long, HowToFill As FillType) As Boolean           …

Combine Array Elements [Rows or Columns]


here is an updated version of my last  function to combine arrays elements. by using this function user can combine columns as well as rows also ,  Enum Combination     xlRow     xlColumn End Enum Private Function CombineArrayCol(ArrFirst As Variant, ArrAdd As Variant, ArrResult As Variant, CombinationOf As Combination) As Boolean    …

Function to Combine Array


Hi All, Here is a function to combine an array columns in another array. Private Function CombineArrayCol(ArrFirst As Variant, ArrAdd As Variant, ArrResult As Variant) As Boolean              ‘Created By Rajan 4-July-2012          ‘———How to Use——————-        ‘Syntex        ‘CombineArrayCol FirstArray ,SecondArray ,ResultArray                ‘   FirstArray =…

Compile Worksheets :


if you have data in same format on multiple worksheets and you want to compile on a single sheets you can Use this Macro, Sub ConsolidateAllSheets()         Dim wksConsolidate      As Worksheet     Dim wksSheet            As Worksheet     Dim lngLastRow          As Long     Set wksConsolidate = ThisWorkbook.Worksheets.Add     lngLastRow = 1     Application.DisplayAlerts = False    …

Extract Array Elements :


Extract Array : Here is a technique to extract a particular column or Row from a 2D Array ‘============================================================================== Sub TestOnArray()     Dim VarArr     Dim VarArr2     Dim VarArr3     Dim blnFilter As Boolean         Application.EnableEvents = False     VarArr = Range(“Range”)     VarArr2 = Application.Index(VarArr, , Range(“rngCol”).Value)     Range(“rngOutput”).CurrentRegion.ClearContents     If Range(“rngCol”).Value…

Split Data into Multiple Workbook


Hi, Many times we need to make category wise separate files  from data on a worksheet, here is code that will do it for you. Here i have a scenario ,Suppose  we have some data in a Range and 2nd Column of Range have some Categories , we want to bifurcate all Record of all Categories in different Excel…


Hello Friends, Like Microsoft Excel , These days Access also has a great place in organizations, where we need to handle large database by using only SQL or Access DB. So we have create a discussion forum for Ms Access where people can post queries and can get experts advice. Group Link: Access-SQL Group This…

Gauge Chart : [Dial Chart]


Hello world Wanna make an Amazing Gauge Chart ? Its Formally Known as Dial Chart,you can follow some simple steps to create.   I have used some VBA Code to make the needle animated : Here is the Code to Move Needle Sub MoveNiddle()     DoEvents     If Range(“rngNiddlePos”).Value = Range(“Sales”).Value Then Exit Sub  …

Waterfall chart :


A waterfall chart is an intuitive tool to visualize the cumulative effect of sequentially introduced positive or negative values.  here is file you can download the waterfall template [Automated through VBA ] , you just need to give data and press button http://www.2shared.com/file/1kSiTtg7/WaterFall.html hope you  will enjoy.

Looping Through Folders and Files in VBA:


Hi Guys,Many times we need to loop through folders , sub folders and files , i have created a code to get the folder structure of a given path=======================================================================Public ObjFolder As Object Public objFso As Object Public objFldLoop As Object Public lngCounter As Long Public  objFl As Object     ‘===================================================           …

Excel Solver :


As I study the solver, I feel this is elder brother of Goal Seek in Excel ,where we can change only one value with Goal seek , Using Solver we can provide many cell for adjusting value as per our scenario , Below is the Parameter required to solver , We can add many scenario…

Dictionary Object :


when playing with data many times we needs to save some unique values somewhere, that time we can use dictionary object , it does not allow to store duplicate values in it,  Dictionary object Takes two argument at the time of adding data 1) Key .2 ) Value and it returns the value by  ID,…

Column count of Array


Sub ColumnCountofArray()    Dim strArr()    strArr = Range(“A1:D10”)    MsgBox “Column of StrArr =” & UBound(strArr, 2)End Sub

Array Formula ( Extract only Numeric from a string)


Here is a Array formula where A1 has alphanumeric value =SUM(INT(MID(A1,LARGE(–ISNUMBER(INT(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))*ROW(INDIRECT(“1:”&LEN(A1))),ROW(INDIRECT(“1:”&SUM(–ISNUMBER(INT(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1))))))),1)*10^ROW(INDIRECT(“1:”&SUM(–ISNUMBER(INT(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1))))))))/10 Please share your idea to do the same.

Array Formula ( sum of digit in a cell)


Sum of Digit from a cell. Many time we need sum of Digit which are placed in a single cell, we can use this function to get desired result =SUM(INT(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1))) As CSE (Ctrl+Shift+Enter)

Generating PDF from excel


Generating PDF from excel.. Sub CreatePDF()         Dim wksSheet As Worksheet     Dim blnFlag As Boolean     Dim intI As Integer     Dim intResult As Byte         intI = 0     intResult = Application.InputBox(“Type 1 for Entire Workbook and Type 0 For Active Worksheets”)     If intResult = 0 Then     Set wksSheet = ActiveSheet…

How to Calculate Date Difference :


Try this Functionif A1 is Project Start date and A2 is  Project End Data . You can calculate aging between them in Year,Month and date :Use this in A3=DatedDiff(A1,A2) Function DatedDiff(dtStart As Date, dtEnd As Date) As String       Dim ArrStart    Dim ArrEnd    Dim ArrResult       ArrStart = Array(Year(dtStart), Month(dtStart), Day(dtStart)) …

Text to column


Hi,if you are finding a code such like Text to Column . it can help you     Sub TextToColumn()        Dim ArrList        Dim rngCell As Range        Dim strDelimeter    As String        strDelimeter = Application.InputBox(“Please Provide a Delimeter”, “Text To Column”)       …

Hope you will post an excellent solution here


welcome   to this quiz , you need to write a function to get the file extension through either a VBA function or inbuilt excel function FIleName . Book1.Doc Book.book1.xls book.book1.book1.xlsx book.book.book.book1.xlsm Hope you will post a excellent function 🙂

Age Calculation :


If Current Date in A1 and Date of Birth in B2 then Put this Function to Calculate Age: =DATEDIF(B2,B1,”Y”)&” Year “&DATEDIF(B2,B1,”YM”)&” Month “&DATEDIF(B2,B1,”MD”)&” Days”

Get Last Populated Cell Address


Paste it on worksheet Change the Range Address and Press Shift+Ctrl+Enter =IFERROR(ADDRESS(MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0),MAX(IF(LEN(INDIRECT(MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0)&”:”&MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0)))>0,COLUMN(INDIRECT(MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0)&”:”&MAX(IF(LEN(A1:I36),ROW(A1:I36)),0)))))),0)