Copy contents of current cell, make a small change, and paste it into the next column

paulshin
paulshin used Ask the Experts™
on
I never use VBA but I'd like to make a shortcut to copy some text in cell A1,modify it slightly, and have it appear in B1 .
For example, in column A, I enter a message like: Go Team.
In column B, I put this message in a code snippet: "message":"Go team".
I want the cell in column B to be updated after I exit the cell in Column A.
Can any one help? Any info on how I'd install the code would help as well. I have Excel 2007.
Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
Put this formula in cell B1:

="message:" & A1

Kevin
Top Expert 2008
Commented:
Here is a macro solution:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Column = 1 Then
        Target.Offset(0, 1) = "message:" & Target.Value
    End If

End Sub

Place the code in the ThisWorkbook code module. To add VBA code to the ThisWorkbook module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Press CTRL+R to open the VBE project explorer. Find the module named ThisWorkbook and double-click it. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

Kevin

Author

Commented:
Savant,

Thank you! That was awesome. I'm almost there.
Can I use the column name "MyColumn" instead of 1? I tried to use a string as the column name but that failed.
Also, I actually need a string like this:
{"message":"good job"}


HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Author

Commented:
Zorvek,
Sorry for calling you Savant :)
Top Expert 2008
Commented:
I'm taking you literally on the desired result ;-)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Not Application.Intersect(Target, [MyColumn]) Is Nothing Then
        Target.Offset(0, 1) = "{""message"":""" & Target.Value & """}"
    End If

End Sub

Kevin
Top Expert 2008

Commented:
This code will allow a paste of several values at once:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim Cell As Range
   
    For Each Cell In Target.Cells
        If Not Application.Intersect(Cell, [MyColumn]) Is Nothing Then
            Cell.Offset(0, 1).Value = "{""message"":""" & Cell.Value & """}"
        End If
    Next Cell

End Sub

Kevin

Author

Commented:
Thank  you! I couldn't get the last one to work so I  just used the column number approach. Still most excellent. I really appreciate the quick, extensive help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial