How can I create a macro that would add a prefix and suffix to a formula in a range?

Question

I wish to create a macro that requests a "prefix" and "suffix" from the user, as well as a range. The prefix will be placed in front of each formula, while the suffix will be placed at the end of each formula across the entire range. For example, if A1 contains ABC with a prefix =LEFT( and a suffix ,1), the formula in A1 should change from ABC to =LEFT(ABC,1) and therefore only display A.

The best way to provide a user interface for this is via a form. Let's call it a "macro wrapper":

This is what it should look like in action:

Here is my VBA code for cmdApply and cmdCancel buttons:

Private Sub cmdApply_Click()
Dim DataValue As Range

For Each DataValue In Range(redtSelectRange)
  If Left(DataValue.Formula, 1) = "=" Then
    DataValue.Formula = "=" & _
      Trim(txtBefore.Text) & _
      Right(DataValue.Formula, Len(DataValue.Formula) - 1) & _
      Trim(txtAfter.Text)
  Else
    DataValue.Formula = "=" & _
      Trim(txtBefore.Text) & _
      DataValue.Formula & _
      Trim(txtAfter.Text)
  End If
Next DataValue

End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

However, when I compile the above I receive a "Run-time error '1004': Application-defined or object-defined error".

I tried shortening the cmdApply condition to an Immediate If statement:

Private Sub cmdApply_Click()
Dim DataValue As Range

For Each DataValue In Range(redtSelectRange)
  DataValue.Formula = "=" & _
    Trim(txtBefore.Text) & _
    IIf(Left(DataValue.Formula, 1) = "=", Right(DataValue.Formula, Len(DataValue.Formula) - 1), DataValue.Formula) & _
    Trim(txtAfter.Text)
  End If
Next DataValue

End Sub

and even the Intermediate window shows the correct (expected) output for the first entry in the range:

?Trim(txtBefore.Text) & IIf(Left(DataValue.Formula, 1) = "=", Right(DataValue.Formula, Len(DataValue.Formula) - 1), DataValue.Formula) & trim(txtAfter.Text)
=round(1.2,0)

What should I change in my code to properly add/insert the prefix/suffix for each of the range's formulae?

Answer 1

Sub AddText_Prefix_And_Suffix()


Dim rng As Range
Dim Workrng As Range
Dim Prefix As String
Dim Suffix As String


On Error Resume Next
xTitleId = "Range Selector"
Set Workrng = Application.Selection
Set Workrng = Application.InputBox("Range", xTitleId, Workrng.Address, Type:=8)
Prefix = Application.InputBox("Enter Prefix", xTitleId, "Prefix", Type:=2)
Suffix = Application.InputBox("Enter Suffix", xTitleId, "Suffix", Type:=2)


    If Prefix = "False" And Suffix = "False" Then
    MsgBox "User cancelled"
    Else
    For Each rng In Workrng
    rng.Value = Prefix & rng.Value
    rng.Value = rng.Value & Suffix
    Application.ScreenUpdating = False
    Next
    End If

MsgBox "Done"
End Sub

You can use this a simplified version with same result as you are expecting.

this code will ask user to select range to operate, after that it will prompt again for suffix and prefix, using that the existing data will be altered only to selected range

Hope it helps someone

Answer 2

Based on your code and your sample from the image, your .Formula assignment will end up being:

==round(1.2,0)

To fix it:

Private Sub cmdApply_Click()
Dim DataValue As Range

For Each DataValue In Range(redtSelectRange)
  If Left(txtBefore.Text, 1) = "=" Then
    DataValue.Formula = Trim(txtBefore.Text) & _
      Right(DataValue.Formula, Len(DataValue.Formula) - 1) & _
      Trim(txtAfter.Text)
  Else
    DataValue.Formula = "=" & _
      Trim(txtBefore.Text) & _
      DataValue.Formula & _
      Trim(txtAfter.Text)
  End If
Next DataValue

End Sub

Answer 3

Try this:

Private Sub cmdApply_Click()
  Dim DataValue As Range
  For Each DataValue In Range(redtSelectRange)
    'Your code had an additional "=" leading into this string below but in your 
    'example, there was already a leading "=" in the txtBefore.Text. If you
    'don't know if it will always have a leading "=" then add some code to       
    'make sure only 1 is included
    If Left(DataValue.Formula, 1) = "=" Then
      DataValue.Formula = Trim(txtBefore.Text) & _
      Right(DataValue.Formula, Len(DataValue.Formula) - 1) & _
      Trim(txtAfter.Text)
    Else
      DataValue.Formula = "=" & _
      Trim(txtBefore.Text) & _
      DataValue.Formula & _
      Trim(txtAfter.Text)
    End If
  Next 'DataValue isn't required here
End Sub