Results 1 to 20 of 20

If x, autonumber, if y x value .1, x value.2 etc

Threaded View

  1. #1
    Forum Contributor
    Join Date
    08-21-2018
    Location
    Romania
    MS-Off Ver
    2010
    Posts
    178

    If x, autonumber, if y x value .1, x value.2 etc

    hy guys,
    I have this code
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Dim myMax As Long, x, ws As Worksheet
        If (Sh.Name = "Master") + (Sh.Name = "Numbers") Then Exit Sub
        If Intersect(Sh.Columns("b"), Target) Is Nothing Then Exit Sub
        If Target.Count > 1 Then
            With Application
                .EnableEvents = False
                MsgBox "You can not change multiple cells in Column B at a time", vbCritical
                .Undo
                .EnableEvents = True
                Exit Sub
            End With
        End If
        Application.EnableEvents = False
        Target.Offset(, -1).Resize(, 20).Interior.ColorIndex = xlNone
        If (Target.Value = "x") * (Target.Offset(, -1).Value = "") Then
            For Each ws In Worksheets
                If (ws.Name <> "Master") * (ws.Name <> "Numbers") Then
                    myMax = Application.Max(myMax, Application.Max(ws.Columns(1)))
                End If
            Next
            Target.Offset(, -1).Value = myMax + 1
            Sheets("master").Range("a" & Rows.Count).End(xlUp)(2).Resize(, 2).Value = Target.Offset(, -1).Resize(, 2).Value
        ElseIf (Target.Value = "") * (Target.Offset(, -1).Value <> "") Then
            x = Application.Match(Target.Offset(, -1), Sheets("master").Columns(1), 0)
            If IsNumeric(x) Then Sheets("master").Range("a" & x).Resize(, 2).Interior.Color = vbRed
            Target.Offset(, -1).Resize(, 20).Interior.Color = vbRed
        ElseIf (Target.Value = "x") * (Target.Offset(, -1).Value <> "") Then
            x = Application.Match(Target.Offset(, -1), Sheets("master").Columns(1), 0)
            If IsNumeric(x) Then
                Sheets("master").Range("a" & x).Resize(, 20).Interior.ColorIndex = xlNone
            Else
                Sheets("master").Range("a" & Rows.Count).End(xlUp)(2).Resize(, 2).Value = _
                Target.Offset(, -1).Resize(, 2).Value
            End If
        End If
        Application.EnableEvents = True
    End Sub
    If i put "x" in column B from Sheet1, Sheet2, Sheet3 then in column A is allocated a unique number and is copied in sheet Master. Is possible to implement in this code next request :if i put "y" in column B, then in column A is allocated "x" value.1 (x.1,x.2,x.3 etc) ant copy in Master?

    i will give an example in sheet3
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Vba for Autonumber
    By Anto_BT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2016, 08:31 AM
  2. [SOLVED] Autonumber
    By NaomiDawn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-29-2013, 05:48 PM
  3. [SOLVED] Trying to autonumber
    By nonstopcrunchy in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 08-29-2013, 02:51 PM
  4. [SOLVED] Autonumber
    By nik7 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-23-2013, 06:50 PM
  5. UserForm autonumber
    By adeleex in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-23-2011, 09:15 AM
  6. Autonumber
    By jonnygrim in forum Excel General
    Replies: 2
    Last Post: 07-17-2008, 05:22 PM
  7. [SOLVED] autonumber
    By Hardy in forum Excel General
    Replies: 4
    Last Post: 11-24-2005, 01:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1