Formula Challenge #2 :


Hi Guys. Enjoy the new formula challenge ūüôā suppose you have data in A and Column b like 1 ¬†A 2 ¬†B 3 ¬†C 4 you just need to create a Excel Formula to get output below : 1A 1B 1C 2A 2B 2C 3A 3B 3C 4A 4B 4C Enjoy Rajan.

Assign Event to a Bunch of Controls :


Hi Guys , Many times we have a situation when we have array of controls on which we need same event for every control So, this can be simplified by using class module where we will have only one event for all the controls. Example: Suppose, you have many Command bar Buttons on a user…

Hook Chart :


Showing Values on By Hook.  Download the .xlsx from here Regards Rajan

Create Summary Table From Data


This Function Gives you summary of Two Columns Data , see the example below : if you have Table like this : India City 3 India City 4 US City 4 China City 2 China City 3 UK City 2 US City 4 China City 2 Germany City 3 China City 4 India City 3…

Get All Files hyperlinks in Excel :


Do you want a tool which can give you all the links of files at one place? you are welcome here: You just need to select a Root Folder to get all files links in excel file . Download .xlsm from here. Thanks for reading Rajan

Quarter Date Format :


Hi Guys, Many time we need to show Dates in QTR. Format , here is a trick to accomplish that task Date Required Format 01-Jan-12 Q1-12 01-Apr-12 Q2-12 30-Sep-12 Q3-12 01-Oct-12 Q4-12 For this, i used Custom Format in ¬†conditional formatting , you can see the below table for conditional formula and custom format Suppose…

Excel VBA Challenge


Hi Guys,   Challenge is to Delete all Sheets Except one without any loop and mapping table   Keep Excelling 

Get Unique Array From an Array


Function GetUnique(InputArray) As Variant ¬† ¬† ‘========================================== ¬† ¬† ‘Created on 13-Aug-2012 ¬† ¬† ‘Function Return an Array of Unique Values ¬† ¬† ‘========================================== ¬† ¬† Dim lngRow ¬† ¬† ¬†As Long ¬† ¬† Dim lngCol ¬† ¬† ¬†As Long ¬† ¬† Dim ResArray ¬† ¬† With CreateObject(“Scripting.Dictionary”) ¬† ¬† ¬† ¬† If IsArray2Dimensional Then…

Bulls Eye Chart :


Hi All, Just sharing the bulls Eye chart. Bulls Eye chart can be used to see weather parameter is in its desired range or not . I have created this chart by using Radar and PIE , hope you can make use of it .. Click¬†Here¬†to Download the file¬† Your Comment and suggestion are welcome!!…

HTML Body of a Table :


This Function return the HTML Script of any given array , Suppose you are trying to send a mail through outlook and want to paste entire table in Mail Body . there you can use this function. ObjMailItem.HTMLBody=HTMLBody(MyTableArray) ¬† Function HTMLBody(InputArray) As String ¬† ¬† ‘========================================== ¬† ¬† ‘Created on 13-Aug ¬† ¬† ‘Return the…

About IsMissing() :


IsMissing() ¬† IsMissing is a VBA Function to Check , weather an optional arguments is supplied or not in the function. ¬† “To use Ismissing ¬†your optional argument must be Variant Type , if you argument is not variant type Ismissing will not work on that. Because Everything except variant have it defaut value Like…

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…