I tested this on a basic function =1+2 and it worked great, then I tried
it on my more complex equation and it didn't work. So I am just going
to paste the equation that I am working with and see if you can help me
further. Thanks again.

Equation that I need to concatenate and execute.
='I:\SECURED\B&H RECONCILIATIONS\[656158.XLS]SUBMIT'!$L$9

I am pulling the file name (656158) from cell D92.

As we process another form it outputs a new workbook named after the
reference number. Then this file is populated by our A/R devision. I
am trying to create a way that their file that contains this number can
go out to the directory and see if there is a file with that name and
then check the value of a certain cell ($L$9) on that file. If it
comes back with a value then we know a file has been processed with
that matches the entry if not we will get an error message because no
such file exists. I have asked a couple times if there was a way to
just pull the value for a cell and insert it into an equation as above
and no one really had a solution that would work, so I am trying to
create a work around with this concatenate.

Here is the info I have in each cell...

L92: =
M92: 'I:\SECURED\B&H Reconciliations\[
N92: =D92
O92: .xls]Submit'!$L$9

When I run a basic concatenate then it gives me the equation the way I
need it but it does't execute as you know. When I run it with your
custom function it gives me a #VALUE! error. Any suggestions...?

Thanks again for your help, I truly need it!

Mike (Nashville)

Dave Peterson Wrote:
It's actually two lines:

Public Function CONCandEVAL(ParamArray rng())
Dim stemp As String
Dim i As Long
For i = LBound(rng()) To UBound(rng())
stemp = stemp & rng(i)
Next i
CONCandEVAL = Evaluate(stemp)
End Function

miwarren wrote:

I got an error msg on the Dim i As Long For i = LBound(rng()) To
UBound(rng()) part.

Bob Phillips Wrote:
Try a UDF

Public Function CONCandEVAL(ParamArray rng())
Dim stemp As String
Dim i As Long For i = LBound(rng()) To UBound(rng())
stemp = stemp & rng(i)
Next i
CONCandEVAL = Evaluate(stemp)
End Function

Use like =CONCandEVAL(A1,B1,C1,D1)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"miwarren"
wrote in
message
...

Can you use the concatenate function to create the equation and
then
execute that fomula?

Example:

A1 = =
B1 = 1
C1 = +
D1 = 2

=CONCATENATE(A1,B1,C1,D1)

That in turn will give you =1+2 but it doesn't execute it to
give
you
3. Of course my formula is a bit more complex than this, but if
I
can
get this one to work then I can apply it to my problem.

Thanks ahead of time...

Mike (Nashville)


--
miwarren


------------------------------------------------------------------------
miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
View this thread:
http://www.excelforum.com/showthread...hreadid=476780


--
miwarren

------------------------------------------------------------------------
miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
View this thread:
http://www.excelforum.com/showthread...hreadid=476780

--

Dave Peterson


--
miwarren
------------------------------------------------------------------------
miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
View this thread: http://www.excelforum.com/showthread...hreadid=476780