I have something of the same problem with a function I wrote years ago, back
when I was a newbie. (Actually I'm still a newbie, just less of one.) It's
pretty simple: In a sheet with a few hundred cities I have lots of numbers
representing different types of expenses: hotel, apartment, meals, air fare,
general cost-of-living index and so on. I have no direct data in many cases,
so for example I just estimate the cost of an apartment in Tucson by
comparing it to some other city: In Greensboro the cost of an apartment is
65% that of a hotel, so call Tucson's apartment figure 65% of Tucson's hotel
figure.
For this AdjCol needs to know what other city and what other column it's
working with. I hand it the name of the other city ("Greensboro"), and
AdjCol looks it up, and I hand it the column number of the hotel figure (12),
and thus AdjCol can calculate what it needs, like this:
Function AdjCoL(CityName, Optional BaseCol)
If IsMissing(BaseCol) Then BaseCol = 4
SrchRow = Application.Match(CityName, Columns(1), 0) 'Find the city's row
AdjCoL = Cells(SrchRow, Application.Caller.Column).Value _
* Cells(Application.Caller.Row, BaseCol).Value _
/ Cells(SrchRow, BaseCol).Value
End Function
But from time to time certain calls to this function -- never all of them,
only some of them -- suddenly display the #VALUE! error, and I have to get it
to recalculate them by putting the cursor on that cell and hitting <Enter>.
Sometimes one recalculation fixes all the #VALUE! errors; other times I have
to do it to each one. I keep thinking I'll find out one day how to fix that.
But if I understand what Joel is saying, maybe I won't. If the problem is
that I'm feeding AdjCol references to a row and column rather than the row
and column themselves -- to "Greensboro" and 4 rather than R51 and C4 -- then
maybe I'm stuck with the occasional problem, since much of the point of the
function is to save me having to find them manually.
