Needed Macro and Function Help

[GEdwards]

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

 

[Otto Moehrbach]

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

 

[HermH]

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

 

[HermH]

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

 

[GEdwards]

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.