CODEWITHSUNDEEP
excelvba
Nippun Chakravarthy
Nippun Chakravarthy

Reputation: 21

Add a text to an existing cell

I'm trying to create a function in VBA to add a text to an existing cell.

For Example, 

I want to add "Brand" to cells in the first column. I want to use this function in any cell where I enter the formula.

I'm very new to VBA. I tried searching the internet but couldn't find a simple solution for my level. Could anyone please help me with this?  Thank you

Upvotes: 1

Views: 120

Answers (3)

VBasic2008
VBasic2008

Reputation: 55073

A Worksheet Change: Add a Prefix

  • The following code needs to be copied to the sheet module of the worksheet where it is meant to be applied e.g. Sheet1 (not in a standard module e.g. Module1 nor in the ThisWorkbook module).
  • It runs automatically: whatever you attempt to write to A2:A1048576 gets the "Brand " prefix.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const FIRST_CELL As String = "A2"
    Const PREFIX As String = "Brand "
    
    On Error GoTo ClearError
    
    Dim trg As Range
    With Me.Range(FIRST_CELL)
        Set trg = .Resize(Me.Rows.Count - .Row + 1)
    End With
    
    Dim irg As Range: Set irg = Intersect(trg, Target)
    If irg Is Nothing Then Exit Sub
    
    Dim pLen As Long: pLen = Len(PREFIX)
    
    Dim iCell As Range, iString As String
    
    Application.EnableEvents = False
    
    For Each iCell In irg.Cells
        iString = CStr(iCell.Value)
        If Len(iString) > 0 Then
            'If InStr(1, iString, PREFIX, vbTextCompare) <> 1 Then
                iCell.Value = PREFIX & iString
            'End If
        End If
    Next iCell
    
ProcExit:
    On Error Resume Next
       If Not Application.EnableEvents Then Application.EnableEvents = True
    On Error GoTo 0
    Exit Sub
ClearError:
    Debug.Print "Run-time error '" & Err.Number & "':" & vbLf & Err.Description
    Resume ProcExit
End Sub

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

Add a new module in the Visual Basic Editor (VBE).

Add this code to the module:

Option Explicit

Public Sub Add_Brand()

    Dim Cell As Range
    For Each Cell In Selection
        Cell = "Brand " & Cell
    Next Cell

End Sub

Select a range of cells, go to View > Macros on the toolbar and run the Add_Brand macro.

Edit: I should add that if the selected range of cells contain a formula then this will overwrite the formula with the new value.

Edit 2: If you did have formula (not an array formula) I guess you could use this code....

Public Sub Add_Brand()

    Dim Cell As Range
    For Each Cell In Selection
        If Cell.HasFormula Then
            Cell.Formula2 = "=""Brand "" & " & Mid(Cell.Formula2, 2, Len(Cell.Formula2))
        Else
            Cell = "Brand " & Cell
        End If
    Next Cell

End Sub

Upvotes: 1

Claudio
Claudio

Reputation: 85

Select the first cell you want to change before running the code.

Sub insertBrand()

Do While ActiveCell.Value <> ""
    ActiveCell.Value = "Brand " & ActiveCell.Value
    Cells(ActiveCell.Row + 1, ActiveCell.Column).Activate
Loop

End Sub

Upvotes: 0

Related Questions