Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function tests OK but doen't work when called
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function tests OK but doen't work when called
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 "Roel" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function tests OK but doen't work when called
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 -----Original Message----- 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 "Roel" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function tests OK but doen't work when called
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. -- Regards, Tom Ogilvy "Roel" wrote in message ... 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 -----Original Message----- 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 "Roel" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What function tests for whole number (no decimals)? | Excel Worksheet Functions | |||
How do I know which cell a function is called from? | Excel Worksheet Functions | |||
What is this function called?? | Excel Discussion (Misc queries) | |||
Average a group of tests for grade, some tests not taken by all. | Excel Discussion (Misc queries) | |||
Possible to know who has called a function ? | Excel Programming |