VBA Code needed for introducing a CHAR on the left side of a cell with formula

Hello there!

Can somebody help me?

I need a VBA code to insert on the left or in the beginning of a cell, the following character: '

This has to be made instantly on all cells that contain formulas on my spreadsheet (there are dozens of cells with formulas on my spreadsheet, and they are large because they have other nested formulas embedded).

The idea is to "deactivate" all formulas so that when I copy them to a new workbook, there wouldn't be any be link between both workbooks.

I will then need a code to use in order to remove that character from the formulas instantly, so that they get activated again in both workbooks.

Someone else will come up with something more elegant, I'm sure.
But until then, this tiny pieces of code seemed to work for me, with my simple testing.

Please Login or Register  to view this content.

As designed, it deactivated the formulas and then fixed them! It might be all you need.

hi Ed!

Thanks for your reply!

One simple questions: Correct me I am wrong. Are you limiting the quantity of rows and columns on your code? What about if I tell you that my spreadsheet has 1000's of rows and dozens of columns... Of course not all of them have formulas, but I need more room on your code. Could you fix it to cover a whole worksheet?

Here are two subs, one to deactivate, one to activate. Note that, because we're checking every cell on every sheet, this will take a very long time. Recommend you limit to ranges that are actually in use.

Please Login or Register  to view this content.

Sure! thanks for replying back! let me test your code and see how it goes! hold on right there!

In my code, change these lines as needed:

max_rows = 100 ' <--------- change as needed
max_cols = 20 ' <--------- change as needed

For example, if you have 1,000 rows, change the 100 to 1000.

You were right! It was taking too much time to use the whole worksheet as the range on the code, and I didn't want to keep you waiting... so, I changed the lines as needed (thanks for the explanation as for how to do it), and it worked fine! I will keeping changing the lines as needed... it will be faster than using the whole worksheet as the range on the code. Though I guess that using the whole worksheet as the range will be as faster as using specific lines, as long as the worksheet has only a few rows and columns, don't you think?

If the loop is run only against used ranges and with screen updating and auto-calculation turned off, it will run very quickly, unless the workbook has hundreds of sheets or something. Simple formulas can be handled well with find/replace, but nested formulas get much trickier, since multiple equals signs could be used in one formula (i.e., what happens if the character combination you use to replace the equals signs already exists in one of the forumulas?). The loop/hasformula method will only ever rewrite the first equals sign and therefore, by looking only at the first character during deactivation, does not risk unexpected behavior in nested formulas. Just food for thought.

I'm not arguing at all; I just think it's a bit irresponsible to jump in to an already solved thread with a blanket "my way is faster" statement. First of all, the original post asked for VBA code, of which you provided none. Second, my warning of it being slow was to alert the original poster that additional code may be needed to allow for better performance and, since I have no way of knowing which method for determining the "used range" will work best for this poster's scenario, I left that part out. Third, you are right, it is unlikely that a "||" would be used in a formula, but, certainly not impossible. Most importantly, if an "||" was used as a text value in a cell which has no formula; you'd replace it with an equals sign when you went to reactivate the formulas with the second find and replace. Again, food for thought.

that's so true! thanks for bringing it up, glycone!

You were right, gyclone! I set Excel to manually calculate all formulas on my spreadsheet (turn auto-calculation mode off as you said), and then used the code that JosephP provided to me, with a slight variation (see code below), so that I could "cover" the whole spreadsheet rather than a single range, and it worked perfectly: all cells with formulas were deactivated, and it did not take too much time (my spreadsheet has more than 19k rows and more than 300 columns). I definitely prefer using VBA than the find/replacing feature because my spreadsheet has very complex nested formulas, so as you said, I don't want to take the risk of unexpected behavior in nested formulas; but JosephP was making a good point! Thank you both of you! You have saved me from doing the deactivation manually.

Sub Deactivate_Formulas()
'========================
max_rows = 65536 ' <--------- change as needed
max_cols = 256 ' <--------- change as needed

For Row = 1 To max_rows
For Col = 1 To max_cols
temp$ = Cells(Row, Col).Formula
If Left(temp$, 1) = "=" Then
temp$ = "'" + temp$: Cells(Row, Col) = temp$
End If
Next Col
Next Row
End Sub