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

A functionm needs to reference its cells if you want it to update when they
change.

Function FindOldNominal(NomCode,lookup_table as range)

FindOldNominal = WorksheetFunction.VLookup(NomCode, lookup_table,5,false)
End Function


FindNomial(10,IMPORTRANGE)


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"James Cornthwaite" wrote in message
...


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

Hi Bob,

not fully sure what you mean. but to try and clarify

I have defined IMPORTRANGE as a global constant via INSERT, NAME, DEFINE and
then created a range called "IMPORTRANGE" refering to another worsksheet but
within the same workbook.

as i mentioned the function written in the macro works great on first time
entering it, it just doesnt update its return value to reflect any changes
made in the defined range, "IMPORTRANGE", without specifically prompting it
to (i.e. via pressing enter again on the cell).


Would be really greatful for any help. Not an expert with macros at all.

Thanks
James

"Bob Phillips" wrote in message
...
A functionm needs to reference its cells if you want it to update when they
change.

Function FindOldNominal(NomCode,lookup_table as range)

FindOldNominal = WorksheetFunction.VLookup(NomCode, lookup_table,5,false)
End Function


FindNomial(10,IMPORTRANGE)


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"James Cornthwaite" wrote in message
...


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

I understood what IMPORTRANGE was.

As I said, it will work first time, because entering or updating a formula
forces a sheet recalculation. If you want it to recalculate when the range
is changed, you have to let Excel know that the function uses that range,
that is you have to pass it to the function as a parameter. To be able to
pass that range as a parameter, the function has to have a range argument.
That is why I added a lookup_table argument to the function declaration,
ands used that in the function rather than Range("IMPORTRANGE").

Try it and see.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"James Cornthwaite" wrote in message
...
Hi Bob,

not fully sure what you mean. but to try and clarify

I have defined IMPORTRANGE as a global constant via INSERT, NAME, DEFINE
and
then created a range called "IMPORTRANGE" refering to another worsksheet
but
within the same workbook.

as i mentioned the function written in the macro works great on first time
entering it, it just doesnt update its return value to reflect any changes
made in the defined range, "IMPORTRANGE", without specifically prompting
it
to (i.e. via pressing enter again on the cell).


Would be really greatful for any help. Not an expert with macros at all.

Thanks
James

"Bob Phillips" wrote in message
...
A functionm needs to reference its cells if you want it to update when
they
change.

Function FindOldNominal(NomCode,lookup_table as range)

FindOldNominal = WorksheetFunction.VLookup(NomCode,
lookup_table,5,false)
End Function


FindNomial(10,IMPORTRANGE)


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"James Cornthwaite" wrote in message
...


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