1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro to allow adding rows

Discussion in 'Business Applications' started by ksquirt, Aug 22, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. ksquirt

    ksquirt Thread Starter

    Joined:
    Aug 22, 2013
    Messages:
    2
    I'm not sure what this is called (possibly dynamic named range) so I will try to explain. I am trying to update a macro that was written by somebody else. In the macro he used row numbers. I have added rows within the referenced area and now the macro pulls information from the wrong rows. I want to change the macro to allow for more rows whenever I feel like we need to add them. The piece of the code that I want to update is below. If I change the row numbers, it works properly, but I need them to allow for movement. I'm not sure how the rest of the macro uses this piece, so if I need to post the rest of the macro or the actual document, please let me know.

    'Project Level Documents
    rowHeader = 1
    rowPDDstr = 2
    rowPDDend = 5
    rowTOGPMstr = 6
    rowTOGPMend = 8
    rowRAstr = 9
    rowRAend = 12
    rowSOWstr = 13
    rowSOWend = 13
    rowTestStrategystr = 14
    rowTestStrategyend = 14
    rowImplementation_and_Transition_Strategystr = 15
    rowImplementation_and_Transition_Strategyend = 15
    rowIssue_Mgmtstr = 16
    rowIssue_Mgmtend = 17
    rowRisk_Mgmtstr = 18
    rowRisk_Mgmtend = 21
    rowChange_Mgmtstr = 22
    rowChange_Mgmtend = 25
    rowProject_Planstr = 26
    rowProject_Planend = 26
    rowProject_Statusstr = 27
    rowProject_Statusend = 27
    rowProject_Reviewstr = 28
    rowProject_Reviewend = 29
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    Hi, Welcome to the forum.
    This is something I tell every poster.
    Please start by telling us which version of Excel you're using.
    What you explain may be clear to you but try and envision somebody who has no idea of what you're intending to achieve.
    I do suggest you attach a sample (copy) of you file with non-private data and add some informtaion there, somehting like showing a 'before' and 'after' so that one us can understand what you need.
     
  3. ksquirt

    ksquirt Thread Starter

    Joined:
    Aug 22, 2013
    Messages:
    2
    When you add data to the 'follow-up' column on the project or the package tab and then run the email macro (button on the first tab) the data does not line up correctly because we added more rows on the project tab. I want the macro to adjust for that. I hope that makes sense. Thanks all!
     

    Attached Files:

  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    So you're talking about the 'ProjectPrep' routine in the 'emailResults' module. Please, in the future post all of the code. I'm assuming only that routine is what you want us to look at. In looking at the data structure for your Project Level Documents sheet, there's really not much we can use to identify where the sections start/end. The only difference is the formatting, which we can use, but I don't necessarily trust. We could either look for the specific formatting, or the lack thereof. This is inherently dangerous if someone decides to change formatting. The other thing you could do is look for the text and set those rows into an array at run-time.

    A side issue, but one to think about, is the fact that you have these (or your predecessor wrote these) as variables but is treating them as constants. I would personally have used them as constants (if I were to do it this way, which I wouldn't).

    You can shorten your code, assuming you want to go by the interior color of the cell being the definition of what should constitute a new column in your Follup-Up Items list, with this...

    Code:
        Dim wsPLD As Worksheet
        Dim wsFUI As Worksheet
        Dim aFUI(1 To 1, 1 To 12) As Variant
        Dim iRow As Long
        Dim iLastRow As Long
        Dim iColCnt As Long
        Set wsPLD = ThisWorkbook.Worksheets("Project Level Documents")
        Set wsFUI = ThisWorkbook.Worksheets("Follow up Items")
        'Check if there is any Follup-Up Items in the list
        If wsPLD.Cells(wsPLD.Rows.Count, intLastCol).End(xlUp).Row = 1 Then Exit Sub
        iLastRow = wsPLD.Cells(1, 1).End(xlDown).Row
        iColCnt = 0
        For iRow = 2 To iLastRow
            If wsPLD.Cells(iRow, 1).Interior.ColorIndex <> xlNone Then
                iColCnt = iColCnt + 1
                If wsPLD.Cells(iRow, intLastCol).Value <> vbNullString Then
                    aFUI(1, iColCnt) = wsPLD.Cells(iRow, intLastCol).Value & vbNewLine
                End If
            Else
                If wsPLD.Cells(iRow, intLastCol).Value <> vbNullString Then
                    aFUI(1, iColCnt) = aFUI(1, iColCnt) & wsPLD.Cells(iRow, intLastCol).Value & vbNewLine
                End If
            End If
        Next iRow
        wsFUI.Range("F2:Q2").Value = aFUI()
        If aFUI(1, 1) <> vbNullString Then
            strProjectLevel = strProjectLevel & "<font face=""Times New Roman"" size=""3"" color=""Black"">Project Definition Document (PDD): </font><p></p>" & _
                "<font face=""Times New Roman"" size=""2"" color=""Blue"">" & aFUI(1, 1) & "</font><p></p>"
        End If
        If aFUI(1, 2) <> vbNullString Then
            strProjectLevel = strProjectLevel & "<font face=""Times New Roman"" size=""3"" color=""Black"">TOG PM Change: </font><p></p>" & _
                "<font face=""Times New Roman"" size=""2"" color=""Blue"">" & aFUI(1, 2) & "</font><p></p>"
        End If
        If aFUI(1, 3) <> vbNullString Then
            strProjectLevel = strProjectLevel & "<font face=""Times New Roman"" size=""3"" color=""Black"">Risk Analysis: </font><p></p>" & _
                "<font face=""Times New Roman"" size=""2"" color=""Blue"">" & aFUI(1, 3) & "</font><p></p>"
        End If
        If aFUI(1, 4) <> vbNullString Then
            strProjectLevel = strProjectLevel & "<font face=""Times New Roman"" size=""3"" color=""Black"">Statement of Work: </font><p></p>" & _
                "<font face=""Times New Roman"" size=""2"" color=""Blue"">" & aFUI(1, 4) & "</font><p></p>"
        End If
        If aFUI(1, 5) <> vbNullString Then
            strProjectLevel = strProjectLevel & "<font face=""Times New Roman"" size=""3"" color=""Black"">Test Strategy: </font><p></p>" & _
                "<font face=""Times New Roman"" size=""2"" color=""Blue"">" & aFUI(1, 5) & "</font><p></p>"
        End If
        If aFUI(1, 6) <> vbNullString Then
            strProjectLevel = strProjectLevel & "<font face=""Times New Roman"" size=""3"" color=""Black"">Implementation and Transition Strategy: </font><p></p>" & _
                "<font face=""Times New Roman"" size=""2"" color=""Blue"">" & aFUI(1, 6) & "</font><p></p>"
        End If
        If aFUI(1, 7) <> vbNullString Then
            strProjectLevel = strProjectLevel & "<font face=""Times New Roman"" size=""3"" color=""Black"">Issue Management: </font><p></p>" & _
                "<font face=""Times New Roman"" size=""2"" color=""Blue"">" & aFUI(1, 7) & "</font><p></p>"
        End If
        If aFUI(1, 8) <> vbNullString Then
            strProjectLevel = strProjectLevel & "<font face=""Times New Roman"" size=""3"" color=""Black"">Risk Management: </font><p></p>" & _
                "<font face=""Times New Roman"" size=""2"" color=""Blue"">" & aFUI(1, 8) & "</font><p></p>"
        End If
        If aFUI(1, 9) <> vbNullString Then
            strProjectLevel = strProjectLevel & "<font face=""Times New Roman"" size=""3"" color=""Black"">Change Management: </font><p></p>" & _
                "<font face=""Times New Roman"" size=""2"" color=""Blue"">" & aFUI(1, 9) & "</font><p></p>"
        End If
        If aFUI(1, 10) <> vbNullString Then
            strProjectLevel = strProjectLevel & "<font face=""Times New Roman"" size=""3"" color=""Black"">Project Plan: </font><p></p>" & _
                "<font face=""Times New Roman"" size=""2"" color=""Blue"">" & aFUI(1, 10) & "</font><p></p>"
        End If
        If aFUI(1, 11) <> vbNullString Then
            strProjectLevel = strProjectLevel & "<font face=""Times New Roman"" size=""3"" color=""Black"">Project Status: </font><p></p>" & _
                "<font face=""Times New Roman"" size=""2"" color=""Blue"">" & aFUI(1, 11) & "</font><p></p>"
        End If
        If aFUI(1, 12) <> vbNullString Then
            strProjectLevel = strProjectLevel & "<font face=""Times New Roman"" size=""3"" color=""Black"">Project Review: </font><p></p>" & _
                "<font face=""Times New Roman"" size=""2"" color=""Blue"">" & aFUI(1, 12) & "</font><p></p>"
        End If
        If strProjectLevel <> "" Then
            ComplianceIssue = True
            strBody = strBody & _
            "<font face=""Times New Roman"" size=""5"" color=""Black""><B>Project Level Documents</B></font><p></p>" & _
            "<font face=""Times New Roman"" size=""2"" color=""Blue"">" & strProjectLevel & "</font><p></p>"
        End If
    This can replace the entire routine, but does utilize 'intLastCol' and 'strProjectLevel' variables you already have defined. What it won't do is clear the Follup-Up Items list if there are no follow up items on the Project Level Documents sheet. That would need to be added.

    HTH
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1106638

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice