VBA? Tack on extra characters after entering a value

    I am hoping someone can help me with this question as I am somewhat new to VBA and Macros. I am trying to create a code where when I enter a number (i.e. 12345) in a column it will automatically tack on -44 at the end (i.e. 12345-44). I was able to accomplish this in the neighboring column using the formula "=REPLACE(A:A,12345,3,-44)" I would prefer that this is an automatic process and does not require me to press any shortcut keys. Any help from all you gurus out there is GREATLY appreciated!!

    Re: VBA? Tack on extra characters after entering a value


    Welcome to the OzGrid posting world.


    The following Event macro (a change detection macro) MUST be placed in the sheet's private VBA module. (While on the sheet RIGHT click on the sheet tab and select "View Code" -- Paste it in this module.)


    It will tack "-44" (without the quotes) on to any entry made in column A.

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Application.EnableEvents = False
    Target = Target.Value & "-44"
    Application.EnableEvents = True
End If
End Sub


    Sample file attached.

    Re: VBA? Tack on extra characters after entering a value


    An simpler way is to create a custom format.


    Use Format menu
    Select Cells
    Select the Number tab
    Select Custom
    Enter
    #"-44"

    Re: VBA? Tack on extra characters after entering a value


    Just a word of warning for the custom format method:


    This will not change the underlying value of the cell. If you enter 20 in A1 the custom format will display '20-44' but a formula in B1 =A1+20 will return 40. (this will actually show '40-44' unless you have applied a format to B1).


    Also this will not work for formulas such as VLOOKUP. If you look for =VLOOKUP("20-44",A1:A9,1,0) you will get an error as the underlying value is only 20.


    Weasel

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login