Macro Query -

  • Thread starter Thread starter James Cornthwaite
  • Start date Start date
J

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)
 
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.
 
or just use:

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

all the best
Jason
 
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.
 
or just use:


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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

My first macro, uses Vlookup but doesnt update unless hit I enter 3
Color contents of a cell accessed by a function? 4
changing the appearance of a cell accessed 1
My first macro, uses Vlookup but doesnt update unless hit enter 1
Add comments via a function - please could you ammend my code/ pseudocode to work 6
Accessing a variable(a range) and modifying it 1
using excel functions in a macro - not familiar with macros at all 6
Tricky one ( variant type text to be unaffected by a minus) 4

Back
Top