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

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