Reputation: 21
I'm trying to create a function in VBA to add a text to an existing cell.
,
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
Reputation: 55073
Sheet1
(not in a standard module e.g. Module1
nor in the ThisWorkbook
module).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
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
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