Replace the first character in a cell from a selected range

[Marc_w90]

Hi all,

I have an array of indirect formulae (K2:N44), which as you can image are slowing down the processing on the sheet and annoying users.
Fortunately, these formula aren't a necessity at all times.

I was hoping to therefore make a macro which would remove the "=" from the first character in the cells and then another macro to add it back in as the first character.
Not used VBA in some years was hoping there was a simple solution.

Thanks,
Marc

 

[Special-K99]

Am not entirely sure your idea (or indeed my solution) is the best method of doing this but I can't think of another.
So...

Sub RemoveEqualSign()
For i = 11 To 14
    For j = 2 To 44
        If Cells(j, i).Formula <> "" Then
            k = Cells(j, i).Formula
            l = Right(k, Len(k) - 1)
            Cells(j, i) = l
        End If
    Next j
Next i
End Sub

Sub AddEqualSign()
For i = 11 To 14
    For j = 2 To 44
        If Cells(j, i).Formula <> "" Then
            k = Cells(j, i).Formula
            l = "=" & k
            Cells(j, i).Formula = l
        End If
    Next j
Next i
End Sub

 

[MARK858]

Do any of the cells in the range K2:N44 have an equals sign in any position other than at the start?

 

[MARK858]

Just having to go out
If the equals sign is only at the start try

Sub RemoveEqualSigns()
  Range("K2:N44").Replace "=", "", xlPart
End Sub

to add it back in try

Sub AddEqualSigns()
    With Range("K2:N44")
        .Value = Evaluate("IF(" & .Address & "<>"""",""=""&" & .Address & ","""")")
    End With
End Sub

 

[Marc_w90]

Unfortunately some of the formulae have "=" within the formula itself as well, hence me wanting to target the first character.

 

[Marc_w90]

Am not entirely sure your idea (or indeed my solution) is the best method of doing this but I can't think of another.
So...

Sub RemoveEqualSign()
For i = 11 To 14
    For j = 2 To 44
        If Cells(j, i).Formula <> "" Then
            k = Cells(j, i).Formula
            l = Right(k, Len(k) - 1)
            Cells(j, i) = l
        End If
    Next j
Next i
End Sub

Sub AddEqualSign()
For i = 11 To 14
    For j = 2 To 44
        If Cells(j, i).Formula <> "" Then
            k = Cells(j, i).Formula
            l = "=" & k
            Cells(j, i).Formula = l
        End If
    Next j
Next i
End Sub

This is very close.
The removal of the "=" sign seems to work fine and breaks my formulae as intended.

However, I can't get the addition of an "=" as the first character to work.
I just get a Run-time error, application defined or object defined error.
Is it because your code implies the text in there is a formula, which now they have all had the "=" sign at the beginning removed, they are not formula?

Thanks

 

[MARK858]

However, I can't get the addition of an "=" as the first character to work.

Try the 2nd code that I posted