Past Special Concatenated formula

[Don]

I have created a cell that concatenates mult cells to create one formula. I
have copied this cell and past special into another cell to try and activate
the formula. I can select the data in the cell and press enter, This means
(I think) I can not create a maco to automate this (when I created the macro,
the select will hold the amount in the macro insted of making it dynamic)?

I want to change the date and have the formula look into a different file
and update for a vlookup.

 

[Pete_UK]

You could use Evaluate within a macro - this takes a string which
represents a formula and evaluates it. Further details in VBA Help.

Hope this helps.

Pete

 

[Don]

Pete - thanks for the suggestion , but not sure how this will help? When I
copy past the parts together and evaluate, then not sure how to get it to
realize it is a formula without selecting it and pressing enter?

Any further help?

 

[Pete_UK]

Perhaps you can give a bit more detail on what you are trying to achieve,
together with a few examples of what you have now?

Pete

 

[Don]

sure , will try.
A1 '=VLOOKUP(B1,'C:\Temp\test\[ ,
A2 test
A3 =TEXT(B3,"mmddyyyy") B3=date of 12/7/2007
A4 =A1&A2&A3&".xls]Sheet1'!$A$2:$B$19,2)"

with data in test120707.xls in folder c:\temp\test\ and looking for what is
in B3 in the test file, should return what is in column B in the test file.
I copy and past A4 onto A5 using past special values and it looks good but
only when I select the complete formula and press enter will the operation
perform and return what I need. I want to change date to select a separate
days file and do something to make it point to a different file and return
the value. Any help?

I take

 

[Pete_UK]

You could try this user-defined function:

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
Endfunction

Use it as:

=eval(A4)

However, I'm not sure if it will work on an external file, and it's a bit
late here for me to try it out. Let me know how you get on. You might need
to remove the equals sign from A1, or do it like this:
=eval(RIGHT(A4,LEN(A4)-1))

Hope this helps.

Pete

sure , will try.
A1 '=VLOOKUP(B1,'C:\Temp\test\[ ,
A2 test
A3 =TEXT(B3,"mmddyyyy") B3=date of 12/7/2007
A4 =A1&A2&A3&".xls]Sheet1'!$A$2:$B$19,2)"

with data in test120707.xls in folder c:\temp\test\ and looking for what
is
in B3 in the test file, should return what is in column B in the test
file.
I copy and past A4 onto A5 using past special values and it looks good but
only when I select the complete formula and press enter will the operation
perform and return what I need. I want to change date to select a
separate
days file and do something to make it point to a different file and return
the value. Any help?

I take

Perhaps you can give a bit more detail on what you are trying to achieve,
together with a few examples of what you have now?

Pete

 

[Don]

appreciate the help, but that did not seem to work either. This could be on
the right path though , so I will forge ahead

You could try this user-defined function:

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
Endfunction

Use it as:

=eval(A4)

However, I'm not sure if it will work on an external file, and it's a bit
late here for me to try it out. Let me know how you get on. You might need
to remove the equals sign from A1, or do it like this:
=eval(RIGHT(A4,LEN(A4)-1))

Hope this helps.

Pete

sure , will try.
A1 '=VLOOKUP(B1,'C:\Temp\test\[ ,
A2 test
A3 =TEXT(B3,"mmddyyyy") B3=date of 12/7/2007
A4 =A1&A2&A3&".xls]Sheet1'!$A$2:$B$19,2)"

with data in test120707.xls in folder c:\temp\test\ and looking for what
is
in B3 in the test file, should return what is in column B in the test
file.
I copy and past A4 onto A5 using past special values and it looks good but
only when I select the complete formula and press enter will the operation
perform and return what I need. I want to change date to select a
separate
days file and do something to make it point to a different file and return
the value. Any help?

I take

Perhaps you can give a bit more detail on what you are trying to achieve,
together with a few examples of what you have now?

Pete

Pete - thanks for the suggestion , but not sure how this will help?
When
I
copy past the parts together and evaluate, then not sure how to get it
to
realize it is a formula without selecting it and pressing enter?

Any further help?

:

You could use Evaluate within a macro - this takes a string which
represents a formula and evaluates it. Further details in VBA Help.

Hope this helps.

Pete

I have created a cell that concatenates mult cells to create one
formula. I
have copied this cell and past special into another cell to try and
activate
the formula. I can select the data in the cell and press enter,
This
means
(I think) I can not create a maco to automate this (when I created
the
macro,
the select will hold the amount in the macro insted of making it
dynamic)?

I want to change the date and have the formula look into a different
file
and update for a vlookup.

 

[Pete_UK]

Okay, Don, thanks for feeding back.

Pete

appreciate the help, but that did not seem to work either. This could be on
the right path though , so I will forge ahead

You could try this user-defined function:

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
Endfunction

Use it as:

However, I'm not sure if it will work on an external file, and it's a bit
late here for me to try it out. Let me know how you get on. You might need
to remove the equals sign from A1, or do it like this:
=eval(RIGHT(A4,LEN(A4)-1))

Hope this helps.

Pete

sure , will try.
A1 '=VLOOKUP(B1,'C:\Temp\test\[ ,
A2 test
A3 =TEXT(B3,"mmddyyyy") B3=date of 12/7/2007
A4 =A1&A2&A3&".xls]Sheet1'!$A$2:$B$19,2)"
with data in test120707.xls in folder c:\temp\test\ and looking for what
is
in B3 in the test file, should return what is in column B in the test
file.
I copy and past A4 onto A5 using past special values and it looks good but
only when I select the complete formula and press enter will the operation
perform and return what I need. I want to change date to select a
separate
days file and do something to make it point to a different file and return
the value. Any help?
I take
:
Perhaps you can give a bit more detail on what you are trying to achieve,
together with a few examples of what you have now?
Pete
Pete - thanks for the suggestion , but not sure how this will help?
When
I
copy past the parts together and evaluate, then not sure how to get it
to
realize it is a formula without selecting it and pressing enter?
Any further help?
:
You could use Evaluate within a macro - this takes a string which
represents a formula and evaluates it. Further details in VBA Help.
Hope this helps.
Pete
I have created a cell that concatenates mult cells to create one
formula. I
have copied this cell and past special into another cell to try and
activate
the formula. I can select the data in the cell and press enter,
This
means
(I think) I can not create a maco to automate this (when I created
the
macro,
the select will hold the amount in the macro insted of making it
dynamic)?
I want to change the date and have the formula look into a different
file
and update for a vlookup.- Hide quoted text -

- Show quoted text -