VLOOKUP function to get multiple match in a row

Steps to insert new module
1. open workbook.
2. Press alt+f11 TO GO TO VBE(visual basic editor).
3. Insert new module.
4. copy code to new module.

Function Multiple_Vlookup(lookup_value As Range, tbl As Range, col_index_num As Integer)

Dim r As Single, strng As String

Application.Volatile

strng = ""

For r = 1 To tbl.Rows.Count

    If lookup_value = tbl.Cells(r, 1) Then

        strng = strng & tbl.Cells(r, col_index_num) & " ,"

        

    End If

Next r

    Multiple_Vlookup = Left(strng, Len(strng) - 2)

End Function

save this workbook as an add-in file(xla or xlam).Make add-in available via excel add-in command bar.

Application of function: =Multiple_vlookup(lookup_value,tbl,col_index_num)
This function is same as normal vlookup function hence should be applied in same manner.

Multiple_vlookup

Function to reverse text string of variable length

UDF or custom function can work in the same way as built in function.
The example shown in picture reverse the text of string .VBA code define a function =Revtxt that when applied reverse the text of cell provided as input. To use this Function this must be stored in active workbook or workbook having this function must be open. You can also add this function to a xlam (add-in) file and make add-in available via excel add-in command bar.

Function RevText(txt As Range)

Application.Volatile

Dim strLength As Integer

Dim strCount As Integer

strLength = Len(txt)

ReverseText = ""

For strCount = strLength To 1 Step -1

RevText = RevText & _

Mid(txt.Value, strCount, 1)

Next strCount

End Function

\AppData\Roaming\Microsoft\AddIns is default location for add-in file

Open any excel file-press alt+f11 to open VBE editor-insert module and paste the code save this as an add-in file or macro enabled file.

In this example cell A1 having value as ABCD when we applies  Revtxt function on B1 output will be DCBA

ABCD

=RevText(A1)

DCBA

Ask about any excel or VBA issue

https://www.facebook.com/pages/powerexcelorg/1385238331734411

Move command button or any object with sheet scroll

Generally we use command button to assign macro to a button or assign it to shape.But while working on a large sheet button disappears as we scroll horizontally or vertically.this piece of could will scroll command button along with sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Not Intersect(Target, Range("I6:K6")) Is Nothing Then
With Me.UPDATE_1
.Left = Target.Left + 20
.Top = Target.Top - 70
.Width = 72
End With
End If
End Sub
we are using selection change event to record any change in sheet hence running the code.target is defined as active cell.i used range("i6:k6") as target.if any change will be made in these cells my command button will move.

Ask a question

https://www.facebook.com/pages/powerexcelorg/1385238331734411

Custom Function or UDF (user defined functions)-2

Adding description to custom functions(UDF)

There are at least 2 ways you can use to add a description to you UDF's, one is relatively well known, while the other is a simple but little known method. Let’s use the better known method first.

Open Up the VBE (Alt+F11) and Select Anywhere Within Your Function Code.

Now Push F2 To Open The "Object Browser".

At The Top Of The Object Browser There Are 2 Drop Down Boxes. Click The Top One And Select "VBAProject".

You Should Now Have All Modules And Global Objects Showing In The "Classes" Box Situated At The Bottom Of The ObjectBrowser.

Click On The Name Of The Module That Houses Your UDF.

In The "Members Of..." Box to the Right You Should See the Names of All Functions and Procedures Within The Selected Module.

Simply Right Click On The Name Of Your UDF And Select "Properties".

Type A Description For Your UDF, Then Click Ok And Then Save.

Custom Function or UDF (user defined functions)-1

Refrence from office.microsoft.com:

Creating functions

If you have a complex calculation that you reuse frequently in Excel, you don't have to repeatedly enter a 

long, complex worksheet formula. Instead, you can create your own worksheet function to perform the 

calculation. You can then use the function to create formulas that are easier to enter and maintain.

To create your own custom functions, you work in Microsoft Visual Basic® for Applications (VBA). VBA is

 a programming language that's built into Excel. VBA is very flexible and can do everything that Excel 

formulas can do, and more.

For example, say you have a complex formula for figuring sales commissions, where the commission 

percentage changes based on several factors such as what products and mix of products a representative

 sells, whether the sales rep is under quota, and the total sales for the quarter. Instead of entering the 

lengthy formula that takes all of these factors into account every time you want to calculate the 

commission for a sale, you can create a custom commission function. Then all you have to type in your commission formulas is the name of your function.

Furthermore, the next time your company changes the rules for how commissions are calculated, you don't have to find and make tedious changes to all of your complex formulas. You only have to make changes in one place, the custom function, to update all the formulas in your workbook.

The following steps show how to create and use a custom function. To keep the example simple, this

 function calculates a sales commission at a flat 6% rate. To take full advantage of a custom function, 

you would substitute more complex VB code for this simple percentage calculation.

On the Tools menu, point to Macro, and then click Visual Basic Editor.
On the Insert menu in the Microsoft Visual Basic window, click Module.
In the Module window, type the code for your function. For example, a function to calculate a 6% sales commission might look like this:

The first line in the example above creates a function named Commission, which performs its calculations

 on a number or cell reference. The function uses a variable, MyNum, to store the number or value from 

the cell. The second line calculates the value of the Commission function by multiplying the number or cell reference (MyNum) by 0.06 (a more sophisticated function could have several lines of code to perform its calculations). The third line ends the function code.

On the File menu, click Close and Return to Microsoft Excel.
On the worksheet, use your function in formulas as you would any worksheet function. For example, you could use the Commission function to calculate the commissions on sales:

A function that you create in this manner is available for use only in the workbook where you create it. 

If you want to use the function elsewhere, you can copy the code for the function to VBA modules in 

other workbooks; or, if you're a developer, you can copy the function into a workbook used as a function

 library and compile that workbook as an add in program.

Tect to csv conversion

A simple trick to extract data from text files

Many times we need to extract data from text files, hence we use text to columns to extract the data.So here is a simple trick most useful for text file having comma separated values.

Just change the extension of file from .txt to .csv and its all done.