If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Needed Macro and Function Help
Using Excel 2003 I have 2 Workbooks.
MY GOAL... WB1, worksheet "Input Data" needs to search for an entry in column B of WB2, worksheet "Data Sheet". If the value IS FOUND, then I need to replace the entire row with new data from WB1 into a destination location in WB2. If the invoice number IS NOT FOUND, then I need to add a complete new entry into WB2. I already have code for WB2 (although currently used in a macro and not a function) that will perform the search and tell me the row/column location an entry is found at, or not at all. MY NEEDS: 1) In the macro from WB1, I need to pass the value to search in WB2. I felt a Function (changing the current code from a macro) would be best for this. What is the correct call for this? I have tried numerous iterations in 2 test sheets to no avail, such as... ReturnValue = Application.Run("WorkbookName!LocatePrevRec Range("B4")") 'where Range("B4") is the value being passed AND ReturnValue = Application.Run("Invoices & Work Estimates.xls!LocatePrevRec I-100415") 'where I-100415 is the value being passed 2) How is the Function in WB2 to be coded to to be able to pick up the input parameter? I tried starting with (but I get an error)... Function LocatePrevRec(incomingValues) 3) How do I ensure the "LocatePrevRec" function does the search within WB2, where the function is located? 4) From WB2, how do I pass back and receive the resulting details from WB2 Function? BTW - both WBooks are open during all this. So in summary, I need WB1 to pass a parameter to WB2; WB2 needs to send back to the caller in WB1 the results. Signed, Desperately Needing Help |
#2
|
|||
|
|||
Needed Macro and Function Help
PLEASE DO NOT RESPOND TO THIS. I should have placed this under "Excel
Programming". It will be posted again. "GEdwards" wrote: Using Excel 2003 I have 2 Workbooks. MY GOAL... WB1, worksheet "Input Data" needs to search for an entry in column B of WB2, worksheet "Data Sheet". If the value IS FOUND, then I need to replace the entire row with new data from WB1 into a destination location in WB2. If the invoice number IS NOT FOUND, then I need to add a complete new entry into WB2. I already have code for WB2 (although currently used in a macro and not a function) that will perform the search and tell me the row/column location an entry is found at, or not at all. MY NEEDS: 1) In the macro from WB1, I need to pass the value to search in WB2. I felt a Function (changing the current code from a macro) would be best for this. What is the correct call for this? I have tried numerous iterations in 2 test sheets to no avail, such as... ReturnValue = Application.Run("WorkbookName!LocatePrevRec Range("B4")") 'where Range("B4") is the value being passed AND ReturnValue = Application.Run("Invoices & Work Estimates.xls!LocatePrevRec I-100415") 'where I-100415 is the value being passed 2) How is the Function in WB2 to be coded to to be able to pick up the input parameter? I tried starting with (but I get an error)... Function LocatePrevRec(incomingValues) 3) How do I ensure the "LocatePrevRec" function does the search within WB2, where the function is located? 4) From WB2, how do I pass back and receive the resulting details from WB2 Function? BTW - both WBooks are open during all this. So in summary, I need WB1 to pass a parameter to WB2; WB2 needs to send back to the caller in WB1 the results. Signed, Desperately Needing Help |
Thread Tools | |
Display Modes | |
|
|