| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
concatenate and then execute?
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 |
|
#2
|
|||
|
|||
|
concatenate and then execute?
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)) miwarren wrote: 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 -- Dave Peterson |
|
#3
|
|||
|
|||
|
concatenate and then execute?
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 |
|
#4
|
|||
|
|||
|
concatenate and then execute?
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 |
|
#5
|
|||
|
|||
|
concatenate and then execute?
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 |
|
#6
|
|||
|
|||
|
concatenate and then execute?
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 |
|
#7
|
|||
|
|||
|
concatenate and then execute?
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). miwarren wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
