I am having problems with my find match function in VBA, this workbook
is linked to another workbook, and the reference cells come from that
workbook, this doesn't always calculate because there has been no event
that triggers the calculation,
Right now I have a macro that goes back to that work book and rewrites
a formula in one of the cells just to trigger the calculation, is there
a more efficient way to do this?

To force a particular sheet or range to recaulculate, you may use the
Calculate method.

For example,

workbooks(book_name).Worksheets(sheet_name).calcul ate

Regards,
Edwin Tam

http://www.vonixx.com


"dmexcel" wrote:

I am having problems with my find match function in VBA, this workbook
is linked to another workbook, and the reference cells come from that
workbook, this doesn't always calculate because there has been no event
that triggers the calculation,
Right now I have a macro that goes back to that work book and rewrites
a formula in one of the cells just to trigger the calculation, is there
a more efficient way to do this?

I would have thought that if the other workbook doesn't recalculate, then
there is nothing to recalculate, so your forcing a recalculation is
superfluous. Why do you think you need to force it?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"dmexcel" wrote in message
oups.com...
I am having problems with my find match function in VBA, this workbook
is linked to another workbook, and the reference cells come from that
workbook, this doesn't always calculate because there has been no event
that triggers the calculation,
Right now I have a macro that goes back to that work book and rewrites
a formula in one of the cells just to trigger the calculation, is there
a more efficient way to do this?

Hi,
If I enter the numbers manually, it works fine,
If I reference the numbers say =thisworkbook,thissheet,this range
When the numbers change, it doesn't always recalculate unless I double
click on the cell or do something to it, to trigger the function

A couple of thoughts:
1) If this is a function procedure called from a worksheet, make sure
all inputs are passed to the function as arguments. This way, Excel
will know how the function fits into its dependency tree and can
calculate it correctly.
2) If this is part of a Sub procedure, then make the procedure part of
the appropriate calculate/change/other event procedure.

Does that help?


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=515197

HHMM!,
I am sure that is what I have to do but don't know how
Andy