Simplfying Vlookup in VBA

[emcevee]

Hi all,
I hope someone will be able to help me
We have a worksheet template that on the first sheet (Named StaticCosts) all the variable values for different costing parameters are held (in a range Named Parms)

In the other worksheets in the workbook staff can reference these via the vlookup function and it works beautifully.

As the vlookup statement is always the same except for the
lookup value, I thought it would be cool to
create a excel custom function to allow the staff to only enter the lookup value and the custom function would return the lookup result after running the vlookup function.

Can anyone offer advice on how to pass the range to the vlookup statement in the custom function.

 

[firefytr]

Maybe something like this ....

Function MYVLOOKUP(varVal as variant) as variant
Dim ws as worksheet, rng as range
set ws = workbooks("MyTemplate.xlt").sheets("StaticCosts")
set rng = ws.range("Parms")
MYVLOOKUP = worksheetfunction.vlookup(varval, rng, 2, 0)
End function

NB: Untested

Set the specifics to yourself.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

Hi,

like this...

function LkUp(rng as range) 
  LkUp = application.vlookup(rng.value, Sheets("StaticCosts").range("Parms"), 2,false)
end function

Skip,
[sub]
[red][/red]
[/sub]

 

[firefytr]

Also, you might want to check if the workbook is open first before the value is calculated.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[emcevee]

Thanks heaps guys, amazingly quick response, i am grateful