VBA to Automatically Increase the Last Used Value in a Column

OldFella · Jul 10th 2017

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.

**Trebor76** · Jul 10th 2017

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

OldFella · Jul 10th 2017

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

Thanks Robert - works like a charm!
I much appreciate the prompt and effective response.

**Trebor76** · Jul 10th 2017

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

Thanks for the feedback and you're welcome

OldFella · Jul 13th 2017

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.

**Trebor76** · Jul 13th 2017

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

OldFella · Jul 13th 2017

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.

OldFella · Aug 17th 2017

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.

**Carim** · Aug 17th 2017

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:

OldFella · Aug 17th 2017

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.

**Carim** · Aug 17th 2017

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

Hello,

You could test following event macro

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 31 Then Exit Sub
If Target.Value = 0 Then Exit Sub
  If Target.Value = -1 Or Target.Value <= 10 Then
    Dim last As Long
    Dim lnid As Long
    Application.EnableEvents = False
    last = Cells(Application.Rows.Count, "A").End(xlUp).Row
    lnid = CLng(Replace(Cells(last, "A"), Left(Cells(last, "A"), 1), ""))
    Range("A" & Target.Row) = Left(Cells(last, "A"), 1) & lnid + 1
    Application.EnableEvents = True
  End If
End Sub

Display More

Hope this will help

OldFella · Aug 17th 2017

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:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
' This module capitalises entries in the specified column(s) or cell
Dim changed As Range, c As Range
Dim cVal
Const myR As String = "P3:P3,X16:X500" 
Set changed = Intersect(Target, Range(myR))
If Not changed Is Nothing Then
Application.EnableEvents = False
For Each c In changed
cVal = c.Value
Select Case True
Case IsEmpty(cVal), IsNumeric(cVal), _
IsDate(cVal), IsError(cVal)
' Do nothing
Case Else
c.Value = UCase(cVal)
End Select
Next c
Application.EnableEvents = True
End If
End Sub

Display More

**Carim** · Aug 17th 2017

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 ...

OldFella · Aug 17th 2017

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.

**Carim** · Aug 17th 2017

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

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub


    Select Case Target.Column
        
        Case 16, 24
        Dim changed As Range, c As Range
        Dim cVal
        Const myR As String = "P3:P3,X16:X500"
        Set changed = Intersect(Target, Range(myR))
        If Not changed Is Nothing Then
            Application.EnableEvents = False
            For Each c In changed
                cVal = c.Value
                Select Case True
                Case IsEmpty(cVal), IsNumeric(cVal), _
                    IsDate(cVal), IsError(cVal)
                     ' Do nothing
                Case Else
                    c.Value = UCase(cVal)
                End Select
            Next c
            Application.EnableEvents = True
        End If
        
        Case 31
        If Target.Value = 0 Then Exit Sub
        If Target.Value = -1 Or Target.Value <= 10 Then
            Dim last As Long
            Dim lnid As Long
            Application.EnableEvents = False
            last = Cells(Application.Rows.Count, "A").End(xlUp).Row
            lnid = CLng(Replace(Cells(last, "A"), Left(Cells(last, "A"), 1), ""))
            Range("A" & Target.Row) = Left(Cells(last, "A"), 1) & lnid + 1
            Application.EnableEvents = True
        End If
    End Select


End Sub

Display More

Hope this will help

OldFella · Aug 18th 2017

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.

**Carim** · Aug 18th 2017

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

OldFella · Aug 19th 2017

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.

**Carim** · Aug 19th 2017

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

Hello,

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

Code

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Count > 1 Then Exit Sub 
    If Target.Column <> 30 Then Exit Sub 
    If Target.Offset(0,1).Value = 0 Then Exit Sub 
    If Target.Offset(0,1).Value = -1 Or Target.Offset(0,1).Value <= 10 Then 
        Dim last As Long 
        Dim lnid As Long 
        Application.EnableEvents = False 
        last = Cells(Application.Rows.Count, "A").End(xlUp).Row 
        lnid = CLng(Replace(Cells(last, "A"), Left(Cells(last, "A"), 1), "")) 
        Range("A" & Target.Row) = Left(Cells(last, "A"), 1) & lnid + 1 
        Application.EnableEvents = True 
    End If 
End Sub

Display More

Hope this will help solve your problem

OldFella · Aug 19th 2017

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.

VBA to Automatically Increase the Last Used Value in a Column

Participate now!

Share