concatenate and then execute?

[miwarren]

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)

 

[Dave Peterson]

There's nothing built into excel that lets you do that.

But you could create a user defined function that did it.

Option Explicit
Function Eval(myStr As String) As Variant
Eval = Application.Evaluate(myStr)
End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

In your example, you could use:
=eval(a1&b1&c1&d1)
or
=eval(CONCATENATE(A1,B1,C1,D1))

 

[Bob Phillips]

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]

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

 

[Dave Peterson]

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]

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

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

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

As we process another form it outputs a new workbook named after th
reference number. Then this file is populated by our A/R devision.
am trying to create a way that their file that contains this number ca
go out to the directory and see if there is a file with that name an
then check the value of a certain cell ($L$9) on that file. If i
comes back with a value then we know a file has been processed wit
that matches the entry if not we will get an error message because n
such file exists. I have asked a couple times if there was a way t
just pull the value for a cell and insert it into an equation as abov
and no one really had a solution that would work, so I am trying t
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
need it but it does't execute as you know. When I run it with you
custom function it gives me a #VALUE! error. Any suggestions...?

Thanks again for your help, I truly need it!

Mike (Nashville)

 

[Dave Peterson]

You're gonna need something else.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

An alternative would be to build that string, then have some subroutine
(assigned to a button???) convert the value to a formula:

Kind of like:

Option Explicit
Sub testme()

Dim myRng As Range

With ActiveSheet
Set myRng = .Range("P92")
With myRng
myRng.Formula = "=" & .Offset(0, -3).Value _
& .Offset(0, -2).Value & .Offset(0, -1).Value
End With
End With

End Sub

You could even build the string in code--you wouldn't need to include the []'s,
!'s, equal sign (I dumped that already).

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)

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

http://www.excelforum.com/showthread.php?threadid=476780