Custom functions?

[JimmyDodger]

Hi,

Is there a way of making a complex formula into a custom function?

Basically I'm going to have to type out the following about 500 times.

=vlookup(A2,Data!A:B,2,false)

where A2 is going to change, this could be any cell.

I want to be able to type out a number of calculations without having to enter the full formula each time. I'm going to have to do about 500 of them and they wille ach be different

eg =custom(A2)+custom(A7)/custom(d5)
=custom(B4)/100

 

[JimmyDodger]

eg, the kinds of formulas i might need to type out are,

=VLOOKUP(A2, Data!A:B, 2,0)/VLOOKUP(B2, Data!A:B, 2,0)*100+VLOOKUP(A3, Data!A:B, 2,0)/VLOOKUP(B3, Data!A:B, 2,0)*100

so being able to do =cust(A2)/cust(B2)*100+cust(A3)/cust(B3)*100

would be a big help especially because I have to do around 500 different formulas similar but slightly different to above.

 

[Diffy]

Why can't you type it out once and then copy the formula down a column?

 

[Norie]

Jim

Why do you need to type all these formulas individually?

Can't you just copy and paste?

If you really do need to type out the individual formulas, for whatever reason, then you would still need to do that with a custom function, though admittedly there'll be less keystrokes.

 

[JimmyDodger]

I can't copy and paste, or fill down, because the formula is different on each line. eg some are multiplication of the vlookup, others plus, etc.

The reason I want to do it is indeed because it would be less keystrokes, and furthermore it would also help prevent mistakes caused by typos, and make the formulas easier to check and or change.

I really apreciate your help, but if you could tell me how to do this rather than telling me why I don't want to I would be really grateful.

 

[klb]

Another option instead of a UDF, use range names for each of your vlookup variations. You will need to carefully edit your references for relative and absolute but it does make it simple when you get it right.

 

[JimmyDodger]

whats wrong with this code? I can't seem to get it to work.

Function cs(cell)

Dim lookuprange As Range
lookuprange = "Sheet3!A:B"

cs = Application.WorksheetFunction.VLookup(cell, lookuprange, 2, 0)

End Function

 

[macleanb]

Dim lookuprange As Range
set lookuprange = range("Sheet3!A:B")

 

[JimmyDodger]

Fantastic! Thanks very much guys!

Just for future reference the code which finally worked is

Function cs(metricID)

Dim lookuprange As Range
Set lookuprange = Range("Sheet3!A:B")

cs = Application.WorksheetFunction.VLookup(metricID, lookuprange, 3, 0)

End Function

 

[macleanb]

Coolio, however I think I also have Nories concern that there is a more fundemental issue if you are having to input 500 of these by hand. Sounds like a big numbr of inputs to have without a pattern that could be automated/structured more...

For example if you have a pattern as to the relative cells to an input you could build some tools which dont require you to go find a similar formula to copy paste:

e.g. Add this code to the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value = "!!1" Then  ' some unique string you are not likely to find accidentally
    Application.EnableEvents = False
    Target.Formula = "=cs(" & Target.Offset(0, -1).Address & ")"
    Application.EnableEvents = True
    
ElseIf Target.Value = "!!2" Then

    Application.EnableEvents = False
    Target.Formula = "=cs(" & Target.Offset(0, -1).Address & ") / " & "cs(" & Target.Offset(0, -2).Address & ")"
    Application.EnableEvents =True

End If

End Sub

Get the idea?

 

[Norie]

Jim

Does that definitely work?

I don't quite see how you can return a value from the 3rd column of a lookup range that only has 2 columns.