The tricky vlookup formulae

HI friends,

We are all ready know about the powerful vlookup formula in excel. I am do some test on this vlookup formula for make a master vlookup formula to lookup on the left most column from a master data sheet. I want to share the master formula with all of you.

Here is some limitation of the formula which are following:

1) In A1 cell of formula sheet put the value of A1 cell of data sheet
2) if you want to lookup on any other column then you must be change the table_array of the vlookup formula and freeze it for just one time
3)Must be put the same value in b1,c1,d1,......... which is same in the data sheet


The following vlookup formula specially describe for finding the column_index_number through the match formula


Formula Vlookup:
=VLOOKUP($A2,'Data Sheet'!$1:$65536,MATCH(C$1,'Data Sheet'!$1:$1,0),1)


here am also attached a file for showing you how you can used this formula.

if you want any clarification leave a comment me

Thank You:


Download: Click Here

Using Auto_open and Auto_close function in macro

Hi All,

First of all HAPPY DIWALI to you.

Today we are learned about two more powerful function of macro in excel.
1) Auto_Open
2)Auto_Close

1)Auto_Open: auto_open is a function in macro of excel. This function call your desired vb code before a excel workbook is open.

2)Auto_Close: auto_close is a function in macro of excel. This function call your desired vb code before a excel workbook closed.


Here am make one one example for both functions. The following are the codes.

Auto_Open function: The following code show a welcome message when ever you open the workbook in which you save the macro code. You can describe your message for the first time you open your workbook.


Sub auto_open()

ThisWorkbook.Activate

Sheets("record").Select

Range("A1").Select

txt = ActiveCell.FormulaR1C1


Do While txt = ""
txt = InputBox("You not describe any msg please write a msg.", "Welcome Message")

Range("A1").Select

ActiveCell.FormulaR1C1 = txt

If txt = "" Then

MsgBox "Please enter a msg", vbOKOnly, "Alert"

End If

Loop

ActiveWorkbook.Save

MsgBox txt, vbOKOnly, "Welcome"

End Sub


Auto_Close function: The following code show a good bye message whenever you closed the workbook on which you save the vb macro code.


Sub auto_close()

ThisWorkbook.Activate

Sheets("record").Select

Range("A2").Select

txt = ActiveCell.FormulaR1C1

Do While txt = ""
txt = InputBox("Describe a good bye msg", "Good Bye Message")

Range("A2").Select

ActiveCell.FormulaR1C1 = txt

If txt = "" Then

MsgBox "Please enter a msg", vbOKOnly, "Alert"

End If

Loop

ActiveWorkbook.Save

MsgBox txt, vbOKOnly, "Good Bye"

End Sub


Try the both code and tell me what do you think about the more powerful function in excel.
You can also download a addins of the same macro code from the following links

Download the addins: Click Here

Rename, Delete a Excel sheet by using macro

Hi All,

Today we are discussed about how to rename or delete a worksheet by using macro.

Most of the people who used excel they want to know a short cut key for renaming or deleting a worksheet

Here i am tell you how you can make a short cut key for deleting or renaming a excel worksheet by using macros.

Open a new work book and then open the visual basic editor from tools menu.

Now insert a new module for insert your code.

Now copy and paste the following codes
For renaming a sheet:

Sub renamesheet()

Dim sheetname As String

sheetname = InputBox("Enter the new sheet Name")

ActiveSheet.Name = sheetname

End Sub


For deleting a sheet with out alerts:

Sub deletesheet()

On Error GoTo msg

Application.DisplayAlerts = True

ActiveSheet.Delete

End




msg:

MsgBox "This is the last sheet in the workbook, You cant delete it", vbOKOnly,
"Alerts"

End

End Sub




save your workbook.

You also can download and use the renaming and deliting work sheet addins from the following link

Rename_delete_sheet: Click Here

Count all the comments in a active excel sheets

Hi all,

I am back again with some new excel tricks.

Today we are talking about how to count all the comments in a active excel sheet.

For doing this we use the macro. Macro is a more power full tools of excel.

For open the visual basic editor in excel do the following.

tools>macro>visual basic editor or press alt+F11

Now insert a new module from insert menu. Now copy and paste the following code under it

Sub CountComments()
commentcount = 0

For Each cell In ActiveSheet.UsedRange

On Error Resume Next

x = cell.Comment.Text

If Err = 0 Then commentcount = commentcount + 1

Next cell

MsgBox "Total Comments " & commentcount, vbOKOnly, "Sudipto Counts it"

End Sub

Now save your workbook.

from the tools menu select macro and the choose CountComments and click on options button from the window. And choose a short cut key for it, in this case i am using ctrl + m.

Insert few comments on the worksheet you want and press ctrl+m and you can see how much comments in the current active worksheet.

If you want to use the function for all of your workbook you can save your macro workbook as a addin.

For any clarification you can send me a mail.

Thank you.


Download the addins for countcomments: Click Here