Function tests OK but doen't work when called

R

Roel

Hello,
I just managed making my first VBA function.
The function was tested in step-by-step mode and works OK
but when I call it from an actual spreadsheet is does
only part of the work.
The function does a lookup on another spreadsheet using
Vlookup, then returns the value of the row where the
match was found, copies the same row and should paste
this row on my active worksheet right from the active
cell. In step-by-step mode the function does everything
including the pasting part, but when I call the function
it will correctly return the value of the lookup row, but
will not do any pasting.
Any help is greatly appreciated.

Roel
 
B

Bob Flanagan

Roel, functions can only modify the value in the cell containing them. They
can not modify other cells or parts of a workbook.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
R

Roel

Thank you, Bob,
I actually used your add-ins for the Vlookup-part. Can my
function call a sub which will modify other parts of the
sheet (doesn't seem like that) or how do I do this
correctly? Still searching through your add-ins ...
(greatly appreciated!)

Roel van der Heide
 
T

Tom Ogilvy

There is no way to do this from a User Defined Function in a worksheet Cell.
You might be able to do this with the calculate event by checking the value
of the cell. However, if you are editing the value of some cell and want
the action to happen, you should probably use the Change Event.

More would need to be known about what triggers the action to give a good
recommendation.
 
Top