VBA Express : Excel - Add String Text to Existing Cells

Excel

Add String Text to Existing Cells

Ease of Use

Easy

Version tested with

2000

Submitted by:

brettdj

Description:

Adds a specified string of text to existing cells.

Discussion:

Suppose you work for a company that uses the manufacturer's part number, followed by your own 6-digit number, or you need to add the date to a group of invoice numbers for your record-keeping purposes. This macro asks you what the 6-digit string is that you want to add, and then adds it to the end of all the values in column A.

Code:

instructions for use

			Option Explicit 
Sub ConA() 
    Dim Myrange As Range, Mystr As String 
    Application.ScreenUpdating = False 
     
    Mystr = InputBox("Enter the 6 number string to be appended to Column A") 
     
     'If there are no constants then an error will be raised.
    On Error Resume Next 
    Set Myrange = Intersect(ActiveSheet.Range("A:A"), ActiveSheet.Cells.SpecialCells(xlConstants)) 
    On Error Goto 0 
     
     'If there are no constants in Column A then exit
    If Myrange Is Nothing Then Exit Sub 
     
    With Myrange 
        .Offset(0, 1).Columns.Insert 
        .Offset(0, 1).FormulaR1C1 = "=Concatenate(RC[-1], """ & Mystr & """)" 
        .Columns(1).EntireColumn.Formula = .Columns(1).Offset(0, 1).EntireColumn.Value 
        .Columns(1).Offset(0, 1).EntireColumn.Delete 
    End With 
     
    Application.ScreenUpdating = True 
End Sub

How to use:

Copy the code above.
Open your workbook.
Hit Alt+F11 to open the Visual Basic Editor (VBE).
From the menu, choose Insert-Module.
Paste the code into the code window at right.
Close the VBE, saving the file if desired.

Test the code:

Run the macro by going to Tools-Macro-Macros and double-click ConA.

Sample File:

addstring.zip 7.52KB

Approved by mdmackillop

This entry has been viewed 240 times.