0

I want to create a udf for a formula I have written on excel. The formula is as follows:

=INDEX('Pivot-LH'!$D$5:$D$1650,SMALL(IF(B93='Pivot-LH'!$A$5:'Pivot-LH'!$A$1650,ROW('Pivot-LH'!$A$5:'Pivot-LH'!$A$1650)-ROW('Pivot-LH'!$A$5)+2),1))

Basically the syntax is to look for cell B93 (variable) through some data on Pivot-LH sheet and return the 1st, 2nd and 3rd values.

I want to define a udf for this and tried to do this by recording a macro. It gave the following result which I modified to enter B93 as a variable called newroute. However this always gives the value zero:

Public Function LH(newroute As Range) As Variant

Selection.FormulaArray = "=INDEX(R5C4:R1650C4,SMALL(IF(newroute=R5C1:R1650C1,ROW(R5C1:R1650C1)-ROW(R5C1)+2),1))"

End Function

Why does it not give the same result as the formula?

Toby Speight
  • 27,591
  • 48
  • 66
  • 103
Ankit Goel
  • 360
  • 1
  • 5
  • 18

2 Answers2

1

If you want to call LH from a worksheet formula, your function can only return a value. It cannot update the sheet directly.

See: https://support.microsoft.com/en-us/kb/170787

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:

  • Insert, delete, or format cells on the spreadsheet.
  • Change another cell's value.
  • Move, rename, delete, or add sheets to a workbook.
  • Change any of the environment options, such as calculation mode or screen views.
  • Add names to a workbook.
  • Set properties or execute most methods.

So you need something like:

Public Function LH(newroute As Range) As Variant

    LH = newroute.Parent.Evaluate("=INDEX(R5C4:R1650C4,SMALL(IF(" & _
                              newroute.Address() & _
            "=R5C1:R1650C1,ROW(R5C1:R1650C1)-ROW(R5C1)+2),1))"

End Function
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thanks a lot @timWilliams. I understand your point. I am still facing the issue that the formula is returning a #Value error. I am now entering: ' Public Function LH(newroute As Range) LH = newroute.Parent.Evaluate("=INDEX('Pivot-LH'!R5C5:R1650C5,SMALL(IF(" & _ newroute.Address() & _ "='Pivot-LH'!R5C2:'Pivot-LH'!R1650C2,ROW('Pivot-LH'!R5C2:'Pivot-LH'!R1650C2)-ROW('Pivot-LH'!R5C2)+1),1))") End Function' I am sure i am making a small mistake somewhere. – Ankit Goel Sep 01 '16 at 03:04
  • Are all of the ranges involved on the same worksheet? – Tim Williams Sep 01 '16 at 04:57
  • They are in different sheets, but even if I put the ranges in the same sheet I continue to get the error. – Ankit Goel Sep 02 '16 at 11:53
  • Debug.print the formula text and try debugging it on the worksheet. – Tim Williams Sep 02 '16 at 14:53
  • Hi @timwilliams, I have created a template worksheet with my issue. Can you please look into it. I will be grateful. [link] (https://www.dropbox.com/s/c7c34uhx3f0k7en/Template.xlsm?dl=0) – Ankit Goel Sep 06 '16 at 05:45
  • It's not clear to me what you're trying to do in that workbook. I hardly ever us R1C1 notation, so maybe you can switch to A1 style. I notice you changed the `.Address()` to `.Value` though: if you do that then you need to quote the value in the formula. – Tim Williams Sep 06 '16 at 15:59
0

Try this

Public Function LH(newroute As Range) As Variant

Selection.FormulaArray = "=INDEX(R5C4:R1650C4,SMALL(IF(" & newroute.address & "=R5C1:R1650C1,ROW(R5C1:R1650C1)-ROW(R5C1)+2),1))"

End Function
h2so4
  • 1,559
  • 1
  • 10
  • 11