Excel Function to Look up Data in another Spreadsheet

  • Thread starter Thread starter Pickles
  • Start date Start date
P

Pickles

I'm trying to write a user-defined Function, so that I can put data int
other (existing) spreadsheets, and read data back from other cells i
that spreadsheet.

I have a number of spreadsheets that do useful things like calculat
prices, by entering various things into the appropriate cells, givin
an answer in another cell....

I now want to enter hundreds of items into this spreadsheet (withou
changing it). I've written a VBA function that can be invoked i
another spreadsheet, that can read particular cells from the firs
spreadsheet, but I can't put data into other cells.

The idea is, I pass parameters to my Function, that I want to ente
into particular cells in the first spreadsheet, and depending on th
value of another parameter read back from another cell. My (ver
simplified) function now looks like this:

Function testfunction(myparameter1, myparameter2)

testfunction = 999

Select Case myparameter2
Case "P", "p"
testfunction = myparameter1 * 9

Case "R", "r"
testfunction = myparameter1 * Range("SecondCell").Value

End Select

Range("MyCell").Value = testfunction

End Function

The last statement cause the function to return #VALUE; without it, i
works OK. Trouble is, I need that statement - or something tha
actaully does what I'm trying to do....

Help?

Thank
 
The last statement cause the function to return #VALUE; without it, it
works OK.
Click to expand...

You probably need to put "on error goto 0" into your function so that the
error is shown in a dialog box rather than being silently discarded. That
way you can see what the error is.
 
Back
Top