You do realize that by doing that, your formulas will no longer work?
You could record a macro to do what you want, ten jut use it whenever you need to?
For a selection of cells, want to insert a ' in front of formulas in each cell.
Additionally I would like another macro to remove ' from all selected cells. Or remove first character.
You do realize that by doing that, your formulas will no longer work?
You could record a macro to do what you want, ten jut use it whenever you need to?
- Posting guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
Regards
Ford
If you have 2013 or later you could use FORMULATEXT function instead of inserting the ' in each cell
(you would of course have to put that function in another column and copy-paste the result)
Sorry I can't post an example - only had excel 2013 in my last job .. new job only has 2010
Another option might be to use "Show Formulas". Find it on the Formula tab/Formula Auditing/. This will show all formulas in your sheet - until you "turn it off"
- Posting guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
Regards
Ford
These two macros should do what you asked for...
Code:Sub InsertApostrophe() Dim Cell As Range For Each Cell In Selection If Len(Cell.PrefixCharacter) = 0 Then If Cell.HasFormula Then Cell.Value = "'" & Cell.Formula End If Next End Sub Sub RemoveApostrophe() Dim Cell As Range For Each Cell In Selection If Len(Cell.PrefixCharacter) > 0 Then Cell.Formula = Cell.Formula If IsError(Cell.Value) Then Cell.FormulaArray = Cell.FormulaArray End If Next End Sub
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
The above code does not handle array-entered formulas correctly... I believe the following code does.
Code:Sub InsertApostrophe() Dim Cell As Range For Each Cell In Selection If Len(Cell.PrefixCharacter) = 0 Then If Cell.HasArray Then Cell.Value = Replace(Cell.Formula, "=", "'{=") & "}" ElseIf Cell.HasFormula Then Cell.Value = "'" & Cell.Formula End If End If Next End Sub Sub RemoveApostrophe() Dim Cell As Range For Each Cell In Selection If Len(Cell.PrefixCharacter) > 0 Then If Left(Cell.Value, 2) = "{=" Then Cell.FormulaArray = "=" & Replace(Mid(Cell.Value, 3), "}", "") Else Cell.Formula = Cell.Formula End If End If Next End Sub
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
Rick thanks. Very nice advisory work.
I don't have any arrays so this is not an issue.
Lets Kis. Keep it simple.
One extra functionality required:
' should not just be in front of formulas it should also be in front of pure numbers or text. Ie. '50 or 'Text
I coded it for formulas only because your opening message said "For a selection of cells, want to insert a ' in front of formulas in each cell." Handling non-formulas appears to be a whole other task because the apostrophe seems to be harder to find and eliminate for constants. I'll look at this problem a little bit later and get back to you then.
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
A follow-up to the comment I just made in Message #8... I can do what you asked for numbers, that does not appear to be a problem, but why do you want to put an apostrophe in front of text values? Nothing visibly changes when you do that and the apostrophe appears to be impossible to remove once placed there.
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
Okay, I figured it out. These two macros will do what you have now asked for...
Code:Sub InsertApostrophe() Dim Cell As Range For Each Cell In Selection If Len(Cell.PrefixCharacter) = 0 Then If Cell.HasArray Then Cell.Value = Replace(Cell.Formula, "=", "'{=") & "}" Else Cell.Value = "'" & Cell.Formula End If End If Next End Sub Sub RemoveApostrophe() Dim Cell As Range, Text As String For Each Cell In Selection If Len(Cell.PrefixCharacter) > 0 Then If Left(Cell.Value, 2) = "{=" Then Cell.FormulaArray = "=" & Replace(Mid(Cell.Value, 3), "}", "") Else Cell.Formula = Cell.Formula End If If Len(Cell.PrefixCharacter) > 0 Then Text = Cell.Value Cell.Clear Cell.Value = Text End If End If Next End Sub
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
Like this thread? Share it with others