Convert Complex Formula in FUNCTION

[Murtaza]

Can anyone convert this into FUNCTION named MI. This function should ask
user for 2 range.

I have tried the following but cant accomplish:

Function MI(range1 as Range, range2 as Range) As Integer
Selection.FormulaArray = _

"=SUM((R[-3]C:R[-1]C)*(LOOKUP(R[-3]C[-1]:R[-1]C[-1],PriceList!R[-3]C[-1]:R[2
9]C[-1],PriceList!R[-3]C:R[29]C)))"
End Function

What I need is something like this
Function MI(range1 as Range, range2 as Range) As Integer
Selection.FormulaArray = _
"=SUM((User will tell range1)*(LOOKUP(user will tell
range2,PriceList!R[-3]C[-1]:R[29]C[-1],PriceList!R[-3]C:R[29]C)))"
End Function

 

[Dave Peterson]

I'm not sure which two ranges you want to pass, but it's best to pass all the
ranges that your formula uses. That way excel knows when to recalculate. If
any cell in any of the ranges used by the UDF changes, then the function will
recalculate.

And since you only wanted to pass two ranges--you could have passed all four if
you they weren't contiguous, I used a function that would be used like:

=mi(D2:E4,pricelist!d2:e34)

And instead of iterating through the cells, I'd just do some validation on the
passed ranges.

I checked to see if the number of columns in each range was 2 and that you only
passed one area to each range.

Then I could let the code do exactly the same as your worksheet function.

Option Explicit
Function MI(range1 As Range, range2 As Range) As Variant

Dim Range1Col1 As Range
Dim Range1Col2 As Range
Dim Range2Col1 As Range
Dim Range2Col2 As Range

If range1.Columns.Count <> 2 _
Or range2.Columns.Count <> 2 _
Or range1.Areas.Count <> 1 _
Or range2.Areas.Count <> 1 Then
MI = CVErr(xlErrRef)
Exit Function
End If

Set Range1Col1 = range1.Columns(1)
Set Range1Col2 = range1.Columns(2)

Set Range2Col1 = range2.Columns(1)
Set Range2Col2 = range2.Columns(2)

MI = Application.Evaluate("sum((" & Range1Col2.Address(external:=True) _
& "*(lookup(" & Range1Col1.Address(external:=True) _
& "," & Range2Col1.Address(external:=True) _
& "," & Range2Col2.Address(external:=True) _
& "))))")

End Function

In fact, you'll find that if you have lots of these functions in your workbook,
it'll slow down. The native worksheet functions (even though it's a longer
formula) will execute much, much faster than the UDF.

(I'd stick with the worksheet function.)

Can anyone convert this into FUNCTION named MI. This function should ask
user for 2 range.

I have tried the following but cant accomplish:

Function MI(range1 as Range, range2 as Range) As Integer
Selection.FormulaArray = _

"=SUM((R[-3]C:R[-1]C)*(LOOKUP(R[-3]C[-1]:R[-1]C[-1],PriceList!R[-3]C[-1]:R[2
9]C[-1],PriceList!R[-3]C:R[29]C)))"
End Function

What I need is something like this
Function MI(range1 as Range, range2 as Range) As Integer
Selection.FormulaArray = _
"=SUM((User will tell range1)*(LOOKUP(user will tell
range2,PriceList!R[-3]C[-1]:R[29]C[-1],PriceList!R[-3]C:R[29]C)))"
End Function

 

[Murtaza]

Thanks for your efforts DAVE!

Please tell me one thing more.....how can i add descriptions for my
arguments i.e. i want to tell the user what is "range1" and "range2" is for
in this function "Function MI([range1] As Range, [range2] As Range) As
Variant"

See when user enter my function from Insert|Function, Excel ask for 2
arguments range1 & range2.

Hope you understand it.
Murtaza

I'm not sure which two ranges you want to pass, but it's best to pass all the
ranges that your formula uses. That way excel knows when to recalculate. If
any cell in any of the ranges used by the UDF changes, then the function will
recalculate.

And since you only wanted to pass two ranges--you could have passed all four if
you they weren't contiguous, I used a function that would be used like:

=mi(D2:E4,pricelist!d2:e34)

And instead of iterating through the cells, I'd just do some validation on the
passed ranges.

I checked to see if the number of columns in each range was 2 and that you only
passed one area to each range.

Then I could let the code do exactly the same as your worksheet function.

Option Explicit
Function MI(range1 As Range, range2 As Range) As Variant

Dim Range1Col1 As Range
Dim Range1Col2 As Range
Dim Range2Col1 As Range
Dim Range2Col2 As Range

If range1.Columns.Count <> 2 _
Or range2.Columns.Count <> 2 _
Or range1.Areas.Count <> 1 _
Or range2.Areas.Count <> 1 Then
MI = CVErr(xlErrRef)
Exit Function
End If

Set Range1Col1 = range1.Columns(1)
Set Range1Col2 = range1.Columns(2)

Set Range2Col1 = range2.Columns(1)
Set Range2Col2 = range2.Columns(2)

MI = Application.Evaluate("sum((" & Range1Col2.Address(external:=True) _
& "*(lookup(" & Range1Col1.Address(external:=True) _
& "," & Range2Col1.Address(external:=True) _
& "," & Range2Col2.Address(external:=True) _
& "))))")

End Function

In fact, you'll find that if you have lots of these functions in your workbook,
it'll slow down. The native worksheet functions (even though it's a longer
formula) will execute much, much faster than the UDF.

(I'd stick with the worksheet function.)

Can anyone convert this into FUNCTION named MI. This function should ask
user for 2 range.

I have tried the following but cant accomplish:

Function MI(range1 as Range, range2 as Range) As Integer
Selection.FormulaArray = _

"=SUM((R[-3]C:R[-1]C)*(LOOKUP(R[-3]C[-1]:R[-1]C[-1],PriceList!R[-3]C[-1]:R[2
9]C[-1],PriceList!R[-3]C:R[29]C)))"
End Function

What I need is something like this
Function MI(range1 as Range, range2 as Range) As Integer
Selection.FormulaArray = _
"=SUM((User will tell range1)*(LOOKUP(user will tell
range2,PriceList!R[-3]C[-1]:R[29]C[-1],PriceList!R[-3]C:R[29]C)))"
End Function

 

[Dave Peterson]

Tools|Macros|Macro...
Type in the function name (you won't be able to click on it)
When Options gets enabled, you've found it (or at least one of them!)

Click on options
Type your description in the description box. You are limited to number of
characters, so you might have to do it right to get a meaningful description.

Then Ok, then cancel.

This will show when they hit the Fx icon, insert|function.

You may want to change the variable names to something meaningful, too. If you
type
=mi(
and hit ctrl-shift-A, you'll see those names.

Thanks for your efforts DAVE!

Please tell me one thing more.....how can i add descriptions for my
arguments i.e. i want to tell the user what is "range1" and "range2" is for
in this function "Function MI([range1] As Range, [range2] As Range) As
Variant"

See when user enter my function from Insert|Function, Excel ask for 2
arguments range1 & range2.

Hope you understand it.
Murtaza

I'm not sure which two ranges you want to pass, but it's best to pass all the
ranges that your formula uses. That way excel knows when to recalculate. If
any cell in any of the ranges used by the UDF changes, then the function will
recalculate.

And since you only wanted to pass two ranges--you could have passed all four if
you they weren't contiguous, I used a function that would be used like:

=mi(D2:E4,pricelist!d2:e34)

And instead of iterating through the cells, I'd just do some validation on the
passed ranges.

I checked to see if the number of columns in each range was 2 and that you only
passed one area to each range.

Then I could let the code do exactly the same as your worksheet function.

Option Explicit
Function MI(range1 As Range, range2 As Range) As Variant

Dim Range1Col1 As Range
Dim Range1Col2 As Range
Dim Range2Col1 As Range
Dim Range2Col2 As Range

If range1.Columns.Count <> 2 _
Or range2.Columns.Count <> 2 _
Or range1.Areas.Count <> 1 _
Or range2.Areas.Count <> 1 Then
MI = CVErr(xlErrRef)
Exit Function
End If

Set Range1Col1 = range1.Columns(1)
Set Range1Col2 = range1.Columns(2)

Set Range2Col1 = range2.Columns(1)
Set Range2Col2 = range2.Columns(2)

MI = Application.Evaluate("sum((" & Range1Col2.Address(external:=True) _
& "*(lookup(" & Range1Col1.Address(external:=True) _
& "," & Range2Col1.Address(external:=True) _
& "," & Range2Col2.Address(external:=True) _
& "))))")

End Function

In fact, you'll find that if you have lots of these functions in your workbook,
it'll slow down. The native worksheet functions (even though it's a longer
formula) will execute much, much faster than the UDF.

(I'd stick with the worksheet function.)

Can anyone convert this into FUNCTION named MI. This function should ask
user for 2 range.

I have tried the following but cant accomplish:

Function MI(range1 as Range, range2 as Range) As Integer
Selection.FormulaArray = _

"=SUM((R[-3]C:R[-1]C)*(LOOKUP(R[-3]C[-1]:R[-1]C[-1],PriceList!R[-3]C[-1]:R[2
9]C[-1],PriceList!R[-3]C:R[29]C)))"
End Function

What I need is something like this
Function MI(range1 as Range, range2 as Range) As Integer
Selection.FormulaArray = _
"=SUM((User will tell range1)*(LOOKUP(user will tell
range2,PriceList!R[-3]C[-1]:R[29]C[-1],PriceList!R[-3]C:R[29]C)))"
End Function

 

[Murtaza]

Again Thanks,
Now I can add the descriptions to my functions, but you have not told me how
can I add descriptions to my variables i.e. range1 & range2, so that user
can understand what being asking.

Thanks for continous help
Murtaza

Tools|Macros|Macro...
Type in the function name (you won't be able to click on it)
When Options gets enabled, you've found it (or at least one of them!)

Click on options
Type your description in the description box. You are limited to number of
characters, so you might have to do it right to get a meaningful description.

Then Ok, then cancel.

This will show when they hit the Fx icon, insert|function.

You may want to change the variable names to something meaningful, too. If you
type
=mi(
and hit ctrl-shift-A, you'll see those names.

Thanks for your efforts DAVE!

Please tell me one thing more.....how can i add descriptions for my
arguments i.e. i want to tell the user what is "range1" and "range2" is for
in this function "Function MI([range1] As Range, [range2] As Range) As
Variant"

See when user enter my function from Insert|Function, Excel ask for 2
arguments range1 & range2.

Hope you understand it.
Murtaza

I'm not sure which two ranges you want to pass, but it's best to pass

all
the

ranges that your formula uses. That way excel knows when to

recalculate.
If

any cell in any of the ranges used by the UDF changes, then the

function
will

recalculate.

And since you only wanted to pass two ranges--you could have passed

all
four if

you they weren't contiguous, I used a function that would be used like:

=mi(D2:E4,pricelist!d2:e34)

And instead of iterating through the cells, I'd just do some

validation on
the

passed ranges.

I checked to see if the number of columns in each range was 2 and that

you
only

passed one area to each range.

Then I could let the code do exactly the same as your worksheet function.

Option Explicit
Function MI(range1 As Range, range2 As Range) As Variant

Dim Range1Col1 As Range
Dim Range1Col2 As Range
Dim Range2Col1 As Range
Dim Range2Col2 As Range

If range1.Columns.Count <> 2 _
Or range2.Columns.Count <> 2 _
Or range1.Areas.Count <> 1 _
Or range2.Areas.Count <> 1 Then
MI = CVErr(xlErrRef)
Exit Function
End If

Set Range1Col1 = range1.Columns(1)
Set Range1Col2 = range1.Columns(2)

Set Range2Col1 = range2.Columns(1)
Set Range2Col2 = range2.Columns(2)

MI = Application.Evaluate("sum((" &

Range1Col2.Address(external:=True)
_

& "*(lookup(" & Range1Col1.Address(external:=True) _
& "," & Range2Col1.Address(external:=True) _
& "," & Range2Col2.Address(external:=True) _
& "))))")

End Function

In fact, you'll find that if you have lots of these functions in your workbook,
it'll slow down. The native worksheet functions (even though it's a longer
formula) will execute much, much faster than the UDF.

(I'd stick with the worksheet function.)

Murtaza wrote:

Can anyone convert this into FUNCTION named MI. This function should ask
user for 2 range.

I have tried the following but cant accomplish:

Function MI(range1 as Range, range2 as Range) As Integer
Selection.FormulaArray = _

"=SUM((R[-3]C:R[-1]C)*(LOOKUP(R[-3]C[-1]:R[-1]C[-1],PriceList!R[-3]C[-1]:R[2

9]C[-1],PriceList!R[-3]C:R[29]C)))"
End Function

What I need is something like this
Function MI(range1 as Range, range2 as Range) As Integer
Selection.FormulaArray = _
"=SUM((User will tell range1)*(LOOKUP(user will tell
range2,PriceList!R[-3]C[-1]:R[29]C[-1],PriceList!R[-3]C:R[29]C)))"
End Function

 

[Dave Peterson]

I'd include them in the range names and function description:

Instead of Range1, you can use a nicer name (RngForXValues).

IIRC, Laurent Longre has a way of doing this (I've never used it):

http://longre.free.fr/english

It's right at the top (FUNCUSTOMIZE.DLL).

Again Thanks,
Now I can add the descriptions to my functions, but you have not told me how
can I add descriptions to my variables i.e. range1 & range2, so that user
can understand what being asking.

Thanks for continous help
Murtaza

Tools|Macros|Macro...
Type in the function name (you won't be able to click on it)
When Options gets enabled, you've found it (or at least one of them!)

Click on options
Type your description in the description box. You are limited to number of
characters, so you might have to do it right to get a meaningful description.

Then Ok, then cancel.

This will show when they hit the Fx icon, insert|function.

You may want to change the variable names to something meaningful, too. If you
type
=mi(
and hit ctrl-shift-A, you'll see those names.

Thanks for your efforts DAVE!

Please tell me one thing more.....how can i add descriptions for my
arguments i.e. i want to tell the user what is "range1" and "range2" is for
in this function "Function MI([range1] As Range, [range2] As Range) As
Variant"

See when user enter my function from Insert|Function, Excel ask for 2
arguments range1 & range2.

Hope you understand it.
Murtaza

I'm not sure which two ranges you want to pass, but it's best to pass all
the
ranges that your formula uses. That way excel knows when to recalculate.
If
any cell in any of the ranges used by the UDF changes, then the function
will
recalculate.

And since you only wanted to pass two ranges--you could have passed all
four if
you they weren't contiguous, I used a function that would be used like:

=mi(D2:E4,pricelist!d2:e34)

And instead of iterating through the cells, I'd just do some validation on
the
passed ranges.

I checked to see if the number of columns in each range was 2 and that you
only
passed one area to each range.

Then I could let the code do exactly the same as your worksheet function.

Option Explicit
Function MI(range1 As Range, range2 As Range) As Variant

Dim Range1Col1 As Range
Dim Range1Col2 As Range
Dim Range2Col1 As Range
Dim Range2Col2 As Range

If range1.Columns.Count <> 2 _
Or range2.Columns.Count <> 2 _
Or range1.Areas.Count <> 1 _
Or range2.Areas.Count <> 1 Then
MI = CVErr(xlErrRef)
Exit Function
End If

Set Range1Col1 = range1.Columns(1)
Set Range1Col2 = range1.Columns(2)

Set Range2Col1 = range2.Columns(1)
Set Range2Col2 = range2.Columns(2)

MI = Application.Evaluate("sum((" & Range1Col2.Address(external:=True)
_
& "*(lookup(" & Range1Col1.Address(external:=True) _
& "," & Range2Col1.Address(external:=True) _
& "," & Range2Col2.Address(external:=True) _
& "))))")

End Function

In fact, you'll find that if you have lots of these functions in your
workbook,
it'll slow down. The native worksheet functions (even though it's a
longer
formula) will execute much, much faster than the UDF.

(I'd stick with the worksheet function.)

Murtaza wrote:

Can anyone convert this into FUNCTION named MI. This function should ask
user for 2 range.

I have tried the following but cant accomplish:

Function MI(range1 as Range, range2 as Range) As Integer
Selection.FormulaArray = _


"=SUM((R[-3]C:R[-1]C)*(LOOKUP(R[-3]C[-1]:R[-1]C[-1],PriceList!R[-3]C[-1]:R[2
9]C[-1],PriceList!R[-3]C:R[29]C)))"
End Function

What I need is something like this
Function MI(range1 as Range, range2 as Range) As Integer
Selection.FormulaArray = _
"=SUM((User will tell range1)*(LOOKUP(user will tell
range2,PriceList!R[-3]C[-1]:R[29]C[-1],PriceList!R[-3]C:R[29]C)))"
End Function