asked on

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

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.

zorvek (Kevin Jones)

Put this formula in cell B1:

="message:" & A1

Kevin

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

paulshin

ASKER

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"}

paulshin

ASKER

Zorvek,
Sorry for calling you Savant :)

SOLUTION

zorvek (Kevin Jones)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

zorvek (Kevin Jones)

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

paulshin

ASKER

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!