VBA to Automatically Increase the Last Used Value in a Column

**Carim** · Aug 19th 2017

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

Hello again,

Thanks for the sample worksheet ...:wink:

Below is the corrected code:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 24 Then Exit Sub
    If Target.Offset(0, 7).Value = 0 Then
      Range("A" & Target.Row).ClearContents: Exit Sub
    End If
    If Target.Offset(0, 7).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

**KjBox** · Aug 19th 2017

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

Or maybe this as an alternative, it will work for change to any cell that affects the value in column AE for that row.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, s As String, i As Integer
    
    If Target.Rows.Count > 1 Then Exit Sub
    
    Set r = Cells(Target.Row, 1).End(xlUp)
    s = Left(r, 1): i = Right(r, Len(r) - 1) + 1
    
    Application.EnableEvents = 0
    If Cells(Target.Row, 31) = 0 Or Cells(Target.Row, 31) = "" Then
        Cells(Target.Row, 1) = ""
    Else
        Cells(Target.Row, 1) = s & i
    End If
    Application.EnableEvents = 1
    
End Sub

Display More

OldFella · Aug 19th 2017

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

Hi Carim
Thank you so much for your perseverance; it's gratifying to have it now working correctly. Could I ask for one final piece of help - I still need to combine it with the coding that capitalizes cell P3 and column X, so that I end up with coding similar to your #15 post.

Hi KjBox
In most situations the data is simply entered in a sequence across the row and never altered, however I do have one worksheet where the data is regularly changed and the col AE cell value likewise changes, so your coding will be very useful for that sheet. Could I ask the same of you as I just did of Carim - as with other sheets, that one also has existing event coding that capitalizes cell P3 and column X, as per my post #12. Could you combine your coding and the existing coding into a single block so that both macros will work happily together.

Again - thank you both.

**KjBox** · Aug 19th 2017

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

Try this

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, s As String, i As Integer
    
    If Target.Rows.Count > 1 Then Exit Sub
    
    If Target.Column > 23 And Target.Column < 31 Then
        Set r = Cells(Target.Row, 1).End(xlUp)
        s = Left(r, 1): i = Right(r, Len(r) - 1) + 1
        
        Application.EnableEvents = 0
        If Cells(Target.Row, 31) = 0 Or Cells(Target.Row, 31) = "" Then
            Cells(Target.Row, 1) = ""
        Else
            Cells(Target.Row, 1) = s & i
        End If
        If Cells(Target.Row, 24) <> "" Or Not IsDate(Cells(Target.Row, 24)) _
        Or Not IsNumeric(Cells(Target.Row, 24)) Or Not IsError(Cells(Target.Row, 24)) Then
            Cells(Target.Row, 24) = UCase(Cells(Target.Row, 24))
        End If
    ElseIf Target.Column = 16 Then
        If Cells(Target.Row, 16) <> "" Or Not IsDate(Cells(Target.Row, 16)) _
        Or Not IsNumeric(Cells(Target.Row, 16)) Or Not IsError(Cells(Target.Row, 16)) Then
            Cells(Target.Row, 16) = UCase(Cells(Target.Row, 16))
        End If
    End If
    Application.EnableEvents = 1
    
End Sub

Display More

**KjBox** · Aug 19th 2017

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

This code would be better

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, s As String, i As Integer
    
    If Target.Rows.Count > 1 Then Exit Sub
    
    If Target.Column > 24 And Target.Column < 31 Then
        Set r = Cells(Target.Row, 1).End(xlUp)
        s = Left(r, 1): i = Right(r, Len(r) - 1) + 1
        
        Application.EnableEvents = 0
        If Cells(Target.Row, 31) = 0 Or Cells(Target.Row, 31) = "" Then
            Cells(Target.Row, 1) = ""
        Else
            Cells(Target.Row, 1) = s & i
        End If
    ElseIf Target.Column = 24 Then
        If Cells(Target.Row, 24) <> "" And Not IsDate(Cells(Target.Row, 24)) _
        And Not IsNumeric(Cells(Target.Row, 24)) And Not IsError(Cells(Target.Row, 24)) Then
            Cells(Target.Row, 24) = UCase(Cells(Target.Row, 24))
        End If
    ElseIf Target.Column = 16 Then
        If Cells(Target.Row, 16) <> "" And Not IsDate(Cells(Target.Row, 16)) _
        And Not IsNumeric(Cells(Target.Row, 16)) And Not IsError(Cells(Target.Row, 16)) Then
            Cells(Target.Row, 16) = UCase(Cells(Target.Row, 16))
        End If
    End If
    Application.EnableEvents = 1
    
End Sub

Display More

**KjBox** · Aug 19th 2017

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

If you put this in the ThisWorkbook Object module then it will work for all sheets in your workbook, you do not need any code for the Worksheet_Change Event procedure in any or the individual Worksheet Object modules.

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim r As Range, s As String, i As Integer
    
    With Sh
        If Target.Rows.Count > 1 Then Exit Sub
        
        If Target.Column > 23 And Target.Column < 31 Then
            Set r = .Cells(Target.Row, 1).End(xlUp)
            s = Left(r, 1): i = Right(r, Len(r) - 1) + 1
            
            Application.EnableEvents = 0
            If .Cells(Target.Row, 31) = 0 Or Cells(Target.Row, 31) = "" Then
                .Cells(Target.Row, 1) = ""
            Else
                .Cells(Target.Row, 1) = s & i
            End If
            If .Cells(Target.Row, 24) <> "" And Not IsDate(.Cells(Target.Row, 24)) _
            And Not IsNumeric(.Cells(Target.Row, 24)) And Not IsError(.Cells(Target.Row, 24)) Then
                .Cells(Target.Row, 24) = UCase(.Cells(Target.Row, 24))
            End If
        ElseIf Target.Column = 16 Then
            If .Cells(Target.Row, 16) <> "" And Not IsDate(.Cells(Target.Row, 16)) _
            And Not IsNumeric(.Cells(Target.Row, 16)) And Not IsError(.Cells(Target.Row, 16)) Then
                .Cells(Target.Row, 16) = UCase(.Cells(Target.Row, 16))
            End If
        End If
    End With
    Application.EnableEvents = 1


End Sub

Display More

OldFella · Aug 20th 2017

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

Fantastic! - you guys ROCK! Carim, KjBox - thank you both for the assistance. I now have worksheets with both the original and the new event coding, and both modules play happily together. Perfect!

**KjBox** · Aug 20th 2017

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

You're welcome.

VBA to Automatically Increase the Last Used Value in a Column

Participate now!

Share

Users Viewing This Thread