These two macros should do what you asked for...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.
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
The above code does not handle array-entered formulas correctly... I believe the following code does.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
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
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.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
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.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
Okay, I figured it out. These two macros will do what you have now asked for...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.
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