VBA to Automatically Increase the Last Used Value in a Column

  • Column C in my worksheet holds place numbers that increase by 1 moving down the column, depending on what data is in that row. For example, if column F of any given row contains "Yes" then the value in col C increases by 1 from the previous value above it. So if F4, F7, F13 and F25 each contain "Yes" then C4 = 1, C7 = 2, C13 = 3, C25 = 4 . . . and so on down the column.
    I need suitable VBA coding that will look back up col C to find the last used value and increment it by 1 if col F in that particular row contains "Yes".
    Many thanks.

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Hi there,


    Try this event macro on the sheet in question:


    Code
    Option Explicit
    Option Compare Text 'Ignores case sensitivity
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 6 And Target.Value = "Yes" Then
            Application.EnableEvents = False
                Range("A" & Target.Row) = Evaluate("MAX(A:A)") + 1
            Application.EnableEvents = True
        End If
    End Sub


    Regards,


    Robert

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    I'd like to now extend the functionality of this coding slightly as I have a similar application on another worksheet. This time though the target cell can have one of three values or it can be blank. The values can be 1, 2 or N. As with the original query, if the target cell contains 1, 2 or N then I'd like the place number incremented by 1.

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    You haven't said which column to check for the change or what column to update so the following is based on Col. F and Col. A respectively - change to suit if necessary:


    Code
    Option Explicit
    Option Compare Text 'Ignores case sensitivity
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 6 And Target.Value Like "[12n]" Then
            Application.EnableEvents = False
            Range("A" & Target.Row) = Evaluate("MAX(A:A)") + 1
            Application.EnableEvents = True
        End If
    End Sub


    Regards,


    Robert

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Thanks Robert - once again it works like a charm.
    Sorry for the non-specific detail; you assumed correctly however, this worksheet is more or less a clone of the original.
    I'm currently adapting the workbook for a slightly different task and may well come up with a further variation. If I get stuck - I'll be back! In the interim, thanks again for your guidance over the past year or so.

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    I've finally progressed to the variation anticipated in my previous post.


    I've now expanded from a single sheet to a series of sheets and because of this I've had to go from a numeric place marker to an alpha-numeric ID.


    As previous, on each sheet, column A contains the alpha-numeric ID, but this time it's based on the value in column AE of that row. The value in col AE will be between -1 and 10, or the cell may be blank. A value of 0 in col AE, or a blank cell, does not require an ID in col A; any numeric value other than 0 requires an ID. Each sheet has a unique alpha character that forms part of the ID, eg. the sheet I currently have active has ID values in col A starting at D1 and currently ending at D127. The next sheet has values in col A from E1 to E86.


    Can the above event coding (Jul 13th) be modified to increment alpha-numeric ID values rather than just numeric values, and to increment the ID based on the value in col AE as described? Data will only ever be entered below the existing rows of data. Data starts at row 16, with headers and various other data in the preceding 15 rows.

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Hello,


    In order to achieve what is required, you will have to expand a bit on the 'alpha' portion of you new alpha.numeric ID ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Hi Carim
    It's just a single letter, so on the first sheet it's from A1 onwards, the next sheet from B1 onwards, and so on. As noted, on the currently active sheet the ID values range from D1 to D127, although there are actually 159 rows of data. 32 rows either have a "0" in col AE or are blank, leaving 127 rows that required an ID number.
    On any given sheet there is just a single alpha character forming part of the ID, and that character is unique to that sheet.
    Hope that clarifies it.

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Hello,


    You could test following event macro



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Hi Carim
    I 've just trialled the coding on one page and no matter what number I have in col AE - it works perfectly! But - I've become accustomed to that by now! Thank you once again.


    One sheet that I'd like to use that coding on already has event coding on it, to capitalize the alpha characters that are entered in two specific ranges. Is it possible to combine your coding with the existing coding so that they both work? The coding that's currently there is:

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Glad you found a solution to your problem ... :wink:


    Forgot to mention you should be using the event macro in ThisWorkbook ... if you need teh feature to be applied to all your worksheets ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Thanks Carim. I've only applied your coding to one worksheet so far and, as I said, it works perfectly there. I was planning to extend it to the other sheets tomorrow so I'll take note of your guidance to add it to ThisWorkbook. At present I have it in the worksheet's module. Thanks again for your on-going guidance.

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Hello again,


    As far as merging the two event macros ... you could test following



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Hi Carim
    My apologies - I posted a response to your updated coding yesterday, advising that the combined coding worked well, but I obviously did something wrong as it's not displaying. However, I have a new issue.
    After a day of using the worksheet, a problem has arisen. If I manually input a value into column AE then column A updates correctly, however in practice the value in AE is not manually input, it's derived from a formula that takes its data from the preceding 10 - 12 cells in the row. I'm guessing that this, presumably, does not constitute a change event. If that's the case, can the coding be modified to behave as described in the earlier post(s) but to be based the output value of a formula, not on a manually input value?
    In case it's relevant, the sequence of events as data is entered across the row is:
    a) the column AE cell is initially blank;
    b) data is entered into columns J, P, Q, R, T and W and the AE cell remains blank;
    c) data is entered into column X and the AE cell displays 0;
    d) data is entered into columns Y, Z, AA and AB and the AE cell continues to display 0
    e) data is entered into column AC and the AE cell displays a changed value;
    f) data is entered into column AD and the AE cell displays its final value.
    Data is always entered in sequence across the row, so as I see it the value finally displayed in column AE after entering data into column AD is the change event that needs to trigger the update to column A.
    I apologise that this is somewhat more involved than I first described, but I simply hadn't understood the distinction between a manually input value and a formula-derived value.

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Hello,


    As an initial test ... try to replace


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)


    with


    Code
    Private Sub Worksheet_Calculate()


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Hi Carim
    The first thing I tried, after making that change, was to change the value in cell P3. That gave a "Run-time error '424': Object required". Running the debugger highlights the first line of code: If Target.Count > 1 Then
    Next, in order to limit the trial to just the coding that relates to col AE I commented out the coding that merges the two event macros (#15) and went back to your initial coding (#11) and replaced the first line. Now, as soon as I enter any value, anywhere, I get that same error pop-up and the same result when I run the debugger.

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Hello,


    Based on you latest comments ... you could test following



    Hope this will help solve your problem

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to Automatically Increase the Last Used Value in a Column


    Hi Carim
    Sorry this is proving to be so troublesome. I made the change as you suggested. It has eliminated the error pop-ups, but it's still not giving the update to column A when the value in col AE changes.
    In case it helps you I've attached a section from the worksheet. I'm using row 23 to test the coding. The display is currently correct; cell A23 should be blank because the value in AE23 is 0, so A23 does not need to be updated. Now - if you change cell X23 from "B" to "S" you will see that AE23 changes from 0 to -0.1, so A23 should now update and show "X2" - but it doesn't. Or - change X23 back to "B" again, and then change AC23 from 1281.66 to 1291.66. AE23 will now display -1.0, which should cause A23 to update and display "X2" - but it doesn't.
    See if that helps you.

Participate now!

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