Combine Strings With Variable that references a formula with VBA

[Peteor]

I am trying to put a string into my formula (which I know would need double quotes). However the formula is already the cell value, please see the below.

Cel.Offset(0, 3).Value = "=""String "" & Cel.Offset(0, 3).Value"

The Cel.Offset(0,3).Value on the right hand side of the = sign is a formula that is already present in the cell. I am trying to break this up into 2 steps because the assembling of the formula also required variables, and the step would get rather complicated. I want Excel/VBA to recognize Cel.Offset(0, 3).Value on the right hand side as a formula, rather than a string. Does anyone have any suggestions? As expected, this currently errors out.

 

[Rick Rothstein]

I am trying to put a string into my formula (which I know would need double quotes). However the formula is already the cell value, please see the below.

Cel.Offset(0, 3).Value = "=""String "" & Cel.Offset(0, 3).Value"

Does this line of code do what you want...

Cel.Offset(0, 3).Formula = Replace(Cel.Offset(0, 3).Formula, "=", "=""String ""&")

 

[Peteor]

I apologize for wasting your time here, but I just a moment ago found my solution. I believe VBA was getting confused having the Cel.Value referenced on both sides of the = sign. I worked around this by using the following (Which is recognizing everything as it should)...Again Thank you for your post!

Cel_String = Cel.Offset(0, 3).Value
Cel.Offset(0, 3).Formula = "String " & Cel_String

Where Cel.Offset(0, 3).Value originally housed a formula.

 

[Rick Rothstein]

I apologize for wasting your time here, but I just a moment ago found my solution. I believe VBA was getting confused having the Cel.Value referenced on both sides of the = sign. I worked around this by using the following (Which is recognizing everything as it should)...Again Thank you for your post!

Cel_String = Cel.Offset(0, 3).Value
Cel.Offset(0, 3).Formula = "String " & Cel_String

Where Cel.Offset(0, 3).Value originally housed a formula.

I thought you were trying to preserve the formula in Cel.Offset(0,3)? Your code above will replace the formula that was there with a text constant.

 

[Peteor]

I am unsure why it works then. Originally, Cel.Offset(0, 3).Value is an Index function which gets information from another sheet. Just this function alone parses the formula and information properly. I was then trying to accomplish this current step which adds the string value. Trying to do it all in 1 step, I was able to get Excel to put everything together, however it would just list the index function as a string. With this solution, it is adding the "String " value to the cell, and still parsing the index function properly. Again though, I do truly appreciate all the help the admins and MVPs provide with these types of issues. I would not know what I know today without your help.

 

[Peteor]

Oh, I see what you mean. The formula is not present though (which is ok). It must have parsed the formula in the Cel_String step, and then placed the string value into the cell on the next step.

 

[Rick Rothstein]

Oh, I see what you mean. The formula is not present though (which is ok).

It's okay? What if a cell that the original formula referenced changes its value? Once the formula has been removed, those later changes will no longer affect the displayed value in Cel.Offset(0,3) whereas if the formula was retained, it would. Just so you know, the code line I posted in Message #2 should successfully modify the existing formula in Cel.Offset(0,3) to add the text you wanted to place at the beginning of the displayed value while still maintaining a formula in Cel.Offset(0,3).