Problem with text functions

Hello All,

I have a problem with text formulas in VBA. The goal of the formula is to remove some parts of formula and brackets when the certain condition is fulfilled. When the formula is related to the cell in the same sheet, all works excellent, but the problem appears when the reference is to another sheet. And how it goes:

The formula starts with: =('Cost Drivers & Assumptions'!J60)/SEK

After i.Formula = Left(i.Formula, Oko - 2) it look like that: =('Cost Drivers & Assumptions'!J60)
After i.Formula = Right(i.Formula, Len(i.Formula) - 2) it is: 'Cost Drivers & Assumptions'!J60)
After i.Formula = Left(i.Formula, Len(i.Formula) - 1) it is : 'Cost Drivers & Assumptions'!J60

Oko = InStr(1, i.Formula, "SEK")

Up to this point everything is ok and now there should only be added "=" sign at the beginning and everything would be fine but it isn't

After i.Formula = "=" & Right(i.Formula, Len(i.Formula)) the "=" is not added and the result in cell is 'Cost Drivers & Assumptions'!J60

Strangely when I add an apostrophe after "=" into code like that : i.Formula = "='" & Right(i.Formula, Len(i.Formula))

It works fine but later on is got screwed after many calculations. Does anybody have an idea why "=" cannot be simply added at the beginning and what might lie behind it? I have no idea. your help is very appreciated.

Best regards,

If i is defined as a range, try this:

Please Login or Register  to view this content.

Cheers, berlan

When building formulas, build the string first. You can then use MsgBox or Debug.Print to view the string to see if it was concatenated and built properly. Sometimes, one can use ()'s to force concatenation on the fly on the right side of the equality sign. I prefer the first method myself.