Append Cell Value

[tjc154]

I have data in Column A starting on row A2 with the following values

GYYC-13852
GYYC-2954
GYYC-69521
etc

If I enter cell values that contain numbers only, is there a way to append the cell values so GYYC- precedes the numbers for the remaining rows?

Thanks,

Tom

 

[Kelbo]

Would it help you to custom format the column with:

"GYYC - "#

This would keep the column numeric should you need to use the numbers for any reason!

Kelbo

 

[Cindy Ellis]

If you actually want to change the value to include the text, the following code in the "Worksheet module" should do what you need:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Columns("A"), Target) Is Nothing Then
    If Application.WorksheetFunction.IsNumber(Target.Value) Then Target.Value = "GYYC-" & Target.Value
End If
End Sub

 

[tjc154]

I inserted a module using the code, but its prompts me to create a macro. I added Sub test() before the code. When I try to run the macro it gives me a Compile error message: Expected End Sub.

Any thoughts?

Thank you

Tom

 

[Rick Rothstein]

I inserted a module using the code, but its prompts me to create a macro. I added Sub test() before the code. When I try to run the macro it gives me a Compile error message: Expected End Sub.

Any thoughts?

Cindy's code is not a macro... it is event code... so it does not go in a standard module, it goes in the sheet module for the sheet that should have this functionality. In the VB editor, find the sheet over in the Project Window and double click it... this will open the code window where you should place her code. Be sure to remove the code from the standard module where you first placed it.

 

[Cindy Ellis]

The code needs to be in the "Worksheet" code page.
Right-click the worksheet tab, and select "View Code" from the context menu.
Paste the whole code that was posted (starting with Private Sub, and ending with End Sub)
Go back to the worksheet, and type any number into column A.
That SHOULD work (it works for me).
Cindy

 

[tjc154]

Rick/Cindy,

Thank you for the clarification. It's working now.

Tom

 

[Peter_SSs]

I would suggest a few changes to the code. As it stands ..

- If you enter a number in column A the code will run twice since when it adds the prefix text, that will trigger the Worksheet_Change event a second time.

- If you alter multiple cells in column A at once (eg select multiple cells and press Delete or Copy more than 1 value and paste or select multiple cell and confirm an entry to all of them with Ctrl+Enter) the code will error.

I would also use the VBA Isnumeric() function rather than call the worksheet function ISNUMBER().

So, considering all that, here is my suggestion:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> Achanged <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>    <SPAN style="color:#00007F">Set</SPAN> Achanged = Intersect(Target, Columns("A"))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Achanged <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Achanged.Cells<br>            s = c.Value<br>            <SPAN style="color:#00007F">If</SPAN> IsNumeric(s) And Len(s) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                c.Value = "GYYC-" & s<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>