Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Needed Macro and Function Help
I got a bit lost in your explanation. Maybe this will help. You can do
whatever you want (search/add/delete/replace/move/etc) in/to any open workbook with code that is located in any ONE open workbook. You don't need to have code in each workbook and then run it all from some other workbook, you can have all the code in one workbook. Does this help at all or did I miss it totally? HTH Otto "GEdwards" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Needed Macro and Function Help
"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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Needed Macro and Function Help
"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 GE: I just started using macros in Excel 2007 - should work similar to 2003. I have 2 sheets: CWS_Temp holds data copied from external web pages that needs formatting since pages aren't formatted identically. CWS_Format gets the formatted results. Field names are not consistently positioned (row/colm), but field entries are always in same row, next column. So I loop thru the list of field names, find each name in CWS_Temp, copy contents of adjacent cell, then come back to CWS_Format and paste from the clipboard. Here's the code which uses CALL to pass parameter to Sub Find_Entry. I'm "assuming" a parameter could be set in the called sub and then returned, but have no need to try that right now. Question to you -- I have one macro_x that executes three others in sequence using Application.Run "'File_Name_rev#.xlsm'!macroN" (N=1,2,3) All macros are contained in File_Name.xlsm. Every time I save the file and increment the rev# in the filename I have to edit the App.Run instruction to get the correct reference. What is proper syntax to reference the Active File? thus, ensuring that you always point to the file that you're now working from. Sheets("CWS_Format").Select For Each c In Sheets("CWS_Format").Range("A1:A68").Cells Call Find_Entry(c.Value) Sheets("CWS_Format").Select c.Activate ActiveCell.Offset(0, 1).Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Next Sub Find_Entry(Phrase) ' Grab field entry adjacent to field name. Sheets("CWS_Temp").Select Range("A1").Select Cells.Find(What:=Phrase, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).Select Selection.Copy End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Needed Macro and Function Help
Otto,
Thanks for your feedback, it is appreciated. I am giving your suggestion some thought and reviewing my code to see what I can do to put everything into 1 macro on 1 sheet. I guess I just really need to ensure I am working with the correct ACTIVE Wbook/sheet to do each portion. Thanks again. "Otto Moehrbach" wrote: I got a bit lost in your explanation. Maybe this will help. You can do whatever you want (search/add/delete/replace/move/etc) in/to any open workbook with code that is located in any ONE open workbook. You don't need to have code in each workbook and then run it all from some other workbook, you can have all the code in one workbook. Does this help at all or did I miss it totally? HTH Otto "GEdwards" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Needed Macro and Function Help | Excel Discussion (Misc queries) | |||
Detailed Function (or Macro) Needed... Help | Excel Worksheet Functions | |||
UDF Function or Macro Needed | Excel Programming | |||
HELP NEEDED FOR IF Function Inside a Macro | Excel Worksheet Functions | |||
A count function or macro needed | Excel Programming |