Results 1 to 4 of 4

Thread: Excel 2013 -> need help having two macro's in a workbook - already have one running

  1. #1
    VBAX Newbie
    Joined
    Jul 2016
    Posts
    2
    Location

    Question Excel 2013 -> need help having two macro's in a workbook - already have one running

    Hi everyone,

    I need help combining two macros into the same sheet. Currently, I am building a status report and want to build some automation behind it. Here is currently the cells that are affected/need to be referenced:

    Column G: QC Status (this is a data validation field - picklist of options. Options on a separate sheet)
    Column H: Developer Status (this is a data validation field - picklist of options. Options on a separate sheet)
    Column J: Next Action Item Due For (open box)
    Column M: Date Completed (Date developer marks Column H "Completed")
    Column N: Week Completed - (the week of the year the date falls in (formula based))
    Column O: QC Date Completed (Date QC'er marks Column G as "Completed")

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        Dim rCell As Range
        Dim rChange As Range
        
        On Error GoTo ErrHandler
        Set rChange = Intersect(Target, Range("H:H"))
        If Not rChange Is Nothing Then
            Application.EnableEvents = False
            For Each rCell In rChange
                If rCell = "Completed" Then
                    With rCell.Offset(0, 5)
                        .Value = Date
                        .NumberFormat = "m/d/yyyy"
                    End With
                Else
                    rCell.Offset(0, 5).Clear
                End If
            Next
            
        End If
    
    
    ExitHandler:
        Set rCell = Nothing
        Set rChange = Nothing
        Application.EnableEvents = True
        Exit Sub
    ErrHandler:
        MsgBox Err.Description
        Resume ExitHandler
    End Sub
    Currently, this marco is putting a date in column M based on Column H being moved to "Completed". I need to be able to run this macro, but also, I want to be able to the date the QC'er mark column G as completed and the date should be in column O.

    I'm not as familiar with Macro's so I wasn't sure how to basically combine the same macro, just pulling different data in different places, on the same sheet.

    I hope this makes sense! Thanks for your help!

  2. #2
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    Write three Subs; Worksheet_Change, MarkDeveloper, and MarkQC

    Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    'Determine where the changed cell is.
    If not Intersect(Target, Range("H:H") Is Nothing Then MarkDeveloper Target 'Pass the changed cell to the sub
    If Not Intersect(Target, Range("G:G")) Is Nothing Then MarkQC Target
    End Sub
    Private Sub MarkDeveloper(Target As Range)
    Application.EnableEvents = False 'Don't let this change be detected
    If Target = "Completed" Then 
                    Target.Offset(0, 5) = Format(Date, "m/d/yyyy")
                Else 
                    Target.Offset(0, 5).Clear 
                End If 
    Application.EnableEvents = True
    End Sub
    Private Sub MarkQC(Target As Range)
    Application.EnableEvents = False
    If Target = "Completed" Then Target.Offset(0, 8) = Format(Date, "m/d/yyyy"
    Application.EnableEvents = True
    End Sub
    Please take the time to read the Forum FAQ

  3. #3
    VBAX Newbie
    Joined
    Jul 2016
    Posts
    2
    Location
    that worked! That you so much for building that for me. Macro's are like a new language to me, so this is EXACTLY what I needed. Really appreciate the help SamT!

  4. #4
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    Do you understand how the code works?
    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

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