Hi I have this small query.


I have written the macro (my first!) as below
Function FindOldNominal(NomCode)

FindOldNominal = WorksheetFunction.VLookup(NomCode,
range("IMPORTRANGE"),5,false)
End Function




This works fine... except..


If I alter the value in the defined range "ImportRange" say from 10 to 20
at nomcode X then

FindOldNominal(X) still says 10, unless I go into the cell where the
formula call of '=FindOldNominal(X)' is and press enter again.

My question is I would like it to update it self automatically like the
sum
function "=sum" does. Is there a reason why this macro isn't or are all
macros like this.
Is there a way round it if this is the case.


Many thanks in anticipation

James


Bob wrote in reponse (BUT IS THIS IS THE ONLY OPTION -i want to try
and keep the formula as simple as possible
other people will be using it)

Hi James:

You should pass the lookup table to the function as a parameter, e.g:

'=============
Function FindOldNominal(NomCode, Table As Range)
FindOldNominal = WorksheetFunction. _
VLookup(NomCode, Table, 2, False)
End Function
'<<=============

Alternatively, and very much as a second best option, Insert the line:

Application.Volatile

at the head of your function. This will ensure that the function will update
every time that the worksheet is recalculated.



---
Regards,
Norman



"James Cornthwaite" wrote in message
...
Hi I have this small query.


I have written the macro (my first!) as below
Function FindOldNominal(NomCode)

FindOldNominal = WorksheetFunction.VLookup(NomCode,
range("IMPORTRANGE"),5,false)
End Function




This works fine... except..


If I alter the value in the defined range "ImportRange" say from 10 to 20
at nomcode X then

FindOldNominal(X) still says 10, unless I go into the cell where the
formula call of '=FindOldNominal(X)' is and press enter again.

My question is I would like it to update it self automatically like the
sum
function "=sum" does. Is there a reason why this macro isn't or are all
macros like this.
Is there a way round it if this is the case.


Many thanks in anticipation

James


Bob wrote in reponse (BUT IS THIS IS THE ONLY OPTION -i want to try
and keep the formula as simple as possible
other people will be using it)

or just use:

FindOldNominal = Application.WorksheetFunction.VLookup(NomCode,
range("IMPORTRANGE"),5,false)

all the best
Jason

thats great thanks

"WhytheQ" wrote in message
oups.com...
or just use:

FindOldNominal = Application.WorksheetFunction.VLookup(NomCode,
range("IMPORTRANGE"),5,false)

all the best
Jason

why not just use vlookup to start with?

--
Don Guillett
SalesAid Software

"James Cornthwaite" wrote in message
...
Hi I have this small query.


I have written the macro (my first!) as below
Function FindOldNominal(NomCode)

FindOldNominal = WorksheetFunction.VLookup(NomCode,
range("IMPORTRANGE"),5,false)
End Function




This works fine... except..


If I alter the value in the defined range "ImportRange" say from 10 to 20
at nomcode X then

FindOldNominal(X) still says 10, unless I go into the cell where the
formula call of '=FindOldNominal(X)' is and press enter again.

My question is I would like it to update it self automatically like the
sum
function "=sum" does. Is there a reason why this macro isn't or are all
macros like this.
Is there a way round it if this is the case.


Many thanks in anticipation

James


Bob wrote in reponse (BUT IS THIS IS THE ONLY OPTION -i want to try
and keep the formula as simple as possible
other people will be using it)

I suppose in the function example i gave it doesnt show
but in other similar ones i use a series of nested ifs and lookups and
because i want to repeatdely use the function only changing the nom code it
is much quicker to use a written function especially since it is for others
to use who dont understand nested ifs or lookups.



"Don Guillett" wrote in message
...
why not just use vlookup to start with?

--
Don Guillett
SalesAid Software

"James Cornthwaite" wrote in message
...
Hi I have this small query.


I have written the macro (my first!) as below
Function FindOldNominal(NomCode)

FindOldNominal = WorksheetFunction.VLookup(NomCode,
range("IMPORTRANGE"),5,false)
End Function




This works fine... except..


If I alter the value in the defined range "ImportRange" say from 10 to 20
at nomcode X then

FindOldNominal(X) still says 10, unless I go into the cell where the
formula call of '=FindOldNominal(X)' is and press enter again.

My question is I would like it to update it self automatically like the
sum
function "=sum" does. Is there a reason why this macro isn't or are all
macros like this.
Is there a way round it if this is the case.


Many thanks in anticipation

James


Bob wrote in reponse (BUT IS THIS IS THE ONLY OPTION -i want to try
and keep the formula as simple as possible
other people will be using it)

or just use:
FindOldNominal = Application.WorksheetFunction.VLookup(NomCode,
range("IMPORTRANGE"),5,false)



What's great about it? Could you clarify.

I don't see any difference from what you had - adding Application adds
nothing? Are you saying that makes the formula update for a change in the
lookup range?
It certainly didn't for me.
--
Regards,
Tom Ogilvy

"James Cornthwaite" wrote in message
...
thats great thanks

"WhytheQ" wrote in message
oups.com...
or just use:

FindOldNominal = Application.WorksheetFunction.VLookup(NomCode,
range("IMPORTRANGE"),5,false)

all the best
Jason