Macro Query -

[James Cornthwaite]

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)

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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)
>

[WhytheQ]

or just use:

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

all the best
Jason

[James Cornthwaite]

thats great thanks

"WhytheQ" <[email protected]> wrote in message
news:[email protected]...
> or just use:
>
> FindOldNominal = Application.WorksheetFunction.VLookup(NomCode,
> range("IMPORTRANGE"),5,false)
>
> all the best
> Jason
>

[Don Guillett]

why not just use vlookup to start with?

--
Don Guillett
SalesAid Software
[email protected]
"James Cornthwaite" <[email protected]> wrote in message
news:[email protected]...
> 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)
>

[James Cornthwaite]

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" <[email protected]> wrote in message
news:[email protected]...
> why not just use vlookup to start with?
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "James Cornthwaite" <[email protected]> wrote in message
> news:[email protected]...
>> 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)
>>
>
>

[Tom Ogilvy]

> > 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" <[email protected]> wrote in message
news:[email protected]...
> thats great thanks
>
> "WhytheQ" <[email protected]> wrote in message
> news:[email protected]...
> > or just use:
> >
> > FindOldNominal = Application.WorksheetFunction.VLookup(NomCode,
> > range("IMPORTRANGE"),5,false)
> >
> > all the best
> > Jason
> >
>
>