Copying text with added "=" (for formulas)

[Bono330]

Hello everyone.
I have a (1) workbook with formulas and I need to paste them to another (2) workbook with macro.
In (1) workbook I have them without “=” sign as they only work when pasted to specific place in (2) workbook. But I cannot make it work with copying.

So for example I have:

VLOOKUP(F2,'[myMakro.xls]Key to 1200'!$A$2:$B$24,2,0)

in (1) workbook with address: Workbooks("myMakro.xls").Worksheets(“Key to 1200”).Range("I1")

Now I try to copy it to (2) workbook to H1 cell using makro with code like:

myFormula = Workbooks("myMakro.xls").Worksheets(“Key to 1200”).Range("I1")
myNewFormula = "=" & myFormula
Range("h1").Value = myNewFormula

Problem is apparently with the “=” sign. I tried to work on that in several ways but nothing works
I would be very grateful for any help.

 

[AlphaFrog]

Try something like this...

myFormula = Workbooks("myMakro.xls").Worksheets("Key to 1200").Range("I1")
Range("H1")[COLOR="Red"].Formula[/COLOR] = "=" & myFormula

 

[Bono330]

Try something like this...

myFormula = Workbooks("myMakro.xls").Worksheets("Key to 1200").Range("I1")
Range("H1")[COLOR=Red].Formula[/COLOR] = "=" & myFormula

Thanks, but unfortunately I get the same error using both .value and .formula.
The error number is 1004 - Application-defined or object-defined error.

 

[jasonb75]

What are the variables myNewFormula and myFormula declared as?

Declaring as String should (hopefully) hold them as text and remove the issue, if not hit Debug, then hold your cursor over the variable myNewFormula in the yellow highlight and look for the error in the formula, you may have some stray inverted commas appearing in there.

 

[AlphaFrog]

If the syntax of your text in I1 is correct and the references in the formula exist, then I know this...
Range("H1").Formula = "=" & myFormula
...will put a formula in a cell H1.


As a test, can you manually type this in cell H1 and see if it returns a value?
=VLOOKUP(F2,'[myMakro.xls]Key to 1200'!$A$2:$B$24,2,0)

If it doesn't return a value, then there is a problem with the text in I1

 

[Bono330]

What are the variables myNewFormula and myFormula declared as?

Declaring as String should (hopefully) hold them as text and remove the issue, if not hit Debug, then hold your cursor over the variable myNewFormula in the yellow highlight and look for the error in the formula, you may have some stray inverted commas appearing in there.

They are both declared as string. When I debug - formula in variable myNewFormula looks ok. I can also see formula is ok by copying it manually to destination cell (in another workbook) and adding there "=".
Then formula works fine so I guess this addition of "=" in vba is not working as one might expect.

 

[jasonb75]

Try changing "=" to "'="

This should allow the code to post the formula to the cell even if it's not valid, then enter the cell and remove the '

If it doesn't accept the formula then you know it's being concatenated incorrectly.

 

[AlphaFrog]

Is the text in cell I1 surrounded by quotes? Something like...
"VLOOKUP(F2,'[myMakro.xls]Key to 1200'!$A$2:$B$24,2,0)"

If yes, then remove the quotes.

 

[Bono330]

If the syntax of your text in I1 is correct and the references in the formula exist, then I know this...
Range("H1").Formula = "=" & myFormula
...will put a formula in a cell H1.


As a test, can you manually type this in cell H1 and see if it returns a value?
=VLOOKUP(F2,'[myMakro.xls]Key to 1200'!$A$2:$B$24,2,0)

If it doesn't return a value, then there is a problem with the text in I1

Sorry, but it does not work for me. I tested if the formula I (from cell "I1") want to enter is correct - copied it and added manually "=".

 

[Bono330]

Try changing "=" to "'="

This should allow the code to post the formula to the cell even if it's not valid, then enter the cell and remove the '

If it doesn't accept the formula then you know it's being concatenated incorrectly.

Yes, this way it works but I cannot do it manually...

BTW after some testing I can see the problem is only with formulas containg functions. When I try to add "=" to some other copied formula like A1+A2 it works.