Prev: Removing spaces from middle of cell
Next: How do I cancel/delete read only for a worksheet?
From: GEdwards on 21 Apr 2010 12:35
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
From: GEdwards on 21 Apr 2010 13:06
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
 | 
Pages: 1
Prev: Removing spaces from middle of cell
Next: How do I cancel/delete read only for a worksheet?