User defined function for index & match

[Veerababu]

Hi all

I am using an udf for index and match commands.

Now, i want the function for specific rows ,columns and range so that i can copy and paste to other cells.

We can do it manually by adding $. But i want the udf to insert automatically.

Kindly help.

Veera

Here is the macro

Function Test(Xa, Aa, rngTable AsRange)
Dim lngRowMatch As Long
Dim lngColMatch As Long
On Error Goto err_handle
With rngTable
lngRowMatch = Application.WorksheetFunction.Match(Xa, .Columns(1), 0)
lngColMatch = Application.WorksheetFunction.Match(Aa, .Rows(1), 0)
Test = .Cells(lngRowMatch, lngColMatch)
End With
Exit Function
err_handle: Test = CVErr(xlErrRef)
End Function

 

[YasserKhalil]

Can you attach your sample workbook?

 

[jeffreyweir]

Using a UDF to do a simple INDEX/MATCH is potentially a very bad idea. These UDFS are much slower than the formula equivalent, and will recalculate any time you delete any cells in the file. So if you have thousands of them or more, you could be in for a very long wait.

 

[Veerababu]

I agree with you. I just started learning udf recently. I am curious to know how to change relative reference of cells to absolute from where udf is called. Kindly help me.

 

[NARAYANK991]

Hi ,

Can you explain what you mean by :

how to change relative reference of cells to absolute from where udf is called.

A UDF is invoked from a worksheet , and it is used the same way you would use a native Excel function ; if you can use SUM(A1:A7) , SUM(A$1:A$7) , SUM($A1:$A7) , SUM($A$1:$A$7) , you can similarly use a UDF by passing its parameters which are range references in which ever form you want used when you copy the formula down / across.

As long as a formula stays in only one cell , it is immaterial whether you use relative addressing or absolute addressing , and since when you copy it down / across , it is Excel which is changing the formula , and not your code , I am not able to understand why the type of addressing should affect the UDF ; can you clarify ?

Narayan

 

[Veerababu]

Hi ,

Can you explain what you mean by :

A UDF is invoked from a worksheet , and it is used the same way you would use a native Excel function ; if you can use SUM(A1:A7) , SUM(A$1:A$7) , SUM($A1:$A7) , SUM($A$1:$A$7) , you can similarly use a UDF by passing its parameters which are range references in which ever form you want used when you copy the formula down / across.

As long as a formula stays in only one cell , it is immaterial whether you use relative addressing or absolute addressing , and since when you copy it down / across , it is Excel which is changing the formula , and not your code , I am not able to understand why the type of addressing should affect the UDF ; can you clarify ?

Narayan

Hi narayan,

Thanks for the reply. Yes i want to use the function to copy to other cells.

When i select the cells by default it gives relative reference and when i copy the formula the range and cell references change like any other excel functions.

when i entered the formula in a cell it look like for example

" =Test(A2,B1,A1:N10) "

If i want to insert dollar sign into the formula automatically when i select the cell for example like

" =Test(A$2,$B1,$A$1:$N$10) "

What are the changes to be done to the udf?

Veera

 

[NARAYANK991]

Hi ,

When you copy the formula , which uses the UDF , across or down , it is Excel which is changing the references ; neither the UDF nor you can stop this , unless you use the appropriate $ signs. No amount of changing the UDF is going to achieve anything.

Narayan

 

[Veerababu]

Hi ,

When you copy the formula , which uses the UDF , across or down , it is Excel which is changing the references ; neither the UDF nor you can stop this , unless you use the appropriate $ signs. No amount of changing the UDF is going to achieve anything.

Narayan

Thank you for the clarification.

Veera