none
My excel macro won't run properly out of project user-form. RRS feed

  • Question

  • Hey guys, 

     So I have this macro I built into a project user-form to organize my excel output from project. The idea is that one button exports the excel file, the second button organizes it to my liking. Here is the code I use to organize the excel file and following the code is my issue.

    \

    Dim xlapp As Excel.Application
    Dim NC As String, tn As String
    Dim xlSheet As Worksheet
    Dim c As Range
    
    Set xlapp = GetObject(, "Excel.Application")
    Set xlSheet = xlapp.ActiveWorkbook.Worksheets(1)
    NC = xlSheet.UsedRange.Rows.Count
    For Each c In xlSheet.Range("A2:A" & NC).Cells
        Debug.Print c.Value
        If c.Offset(0, 2).Value <> "" Then
            tn = c.Offset(0, 5).Value
        Else
            c.Offset(0, 5) = tn
        End If
    Next c
    End Sub

    What the macro does is read column C for the Work Package number and then copies the task name down until the WP number changes again in column c. Now the issue is if I use any decimals with my WP numbers the macro doesn't rename anything it just skips. Can anyone please help me with this? 

    Also on a side note this is the idea behind the macro in brief  ( the slashes represent columns)

    The first task WP would be 1 and all the tasks within that WP would have the first task name as the title, once the WP changed to the number 2 or 2.1, the task name would then be whatever task name was in the row for the WP # change.

    If this is to overly confusing I will post the excel sheet before and after.

    Friday, October 12, 2012 6:47 PM

Answers

  • Jake,

    This macro code should do what you want to organize your Excel spreadsheet.

    Sub JakeExcel()
    Dim xlapp As Excel.Application
    Dim NC As String, tn As String, WP As String
    Dim xlSheet As Worksheet
    Dim c As Range

    Set xlapp = GetObject(, "Excel.Application")
    Set xlSheet = xlapp.ActiveWorkbook.Worksheets(1)
    NC = xlSheet.UsedRange.Rows.Count
    'get starting point work package (WP) and name
    WP = xlSheet.Range("C2")    'Note:".Value" is the default property of a range cell
    tn = xlSheet.Range("F2")
    xlSheet.Range("H2") = tn
    'start comparison loop on third row since there is a header row and row 2 will always
    '   retain the existing name
    For Each c In xlSheet.Range("C3:C" & NC).Cells
        If c <> "" Then
            'if WPs for current line and last line are the same, repeat the name
            If c = c.Offset(-1, 0) Then
                c.Offset(0, 5) = tn
            'if current WP is different or blank, grab the current name
            Else
                tn = c.Offset(0, 3)
                c.Offset(0, 5) = tn
            End If
        'catch repeated blank WPs
        Else: c.Offset(0, 5) = c.Offset(0, 3)
        End If
    Next c
    End Sub

    John

    • Marked as answer by Jake683 Monday, October 29, 2012 4:36 PM
    Sunday, October 28, 2012 10:46 PM

All replies

  • UID CA WP Date Date Name UID CA WP Date Date Name
    1 1 1 Task 1 1 1 1 Task 1
    2 1 1 Task 1.22 2 1 1 Task 1
    3 1 2 Task 2 3 1 2 Task 2
    4 2 2 Task 2.1 4 2 2 Task 2
    5 2 2 Task 2.11 5 2 2 Task 2
    6 2 2 Task 2.13 6 2 2 Task 2
    7 3 3 Task 3 7 3 3 Task 3
    8 4 3 Task 3.1 8 4 3 Task 3
    9 5 3 Task 3.21 9 5 3 Task 3
    10 3 3 Task 3.12 10 3 3 Task 3

    Here is the idea behind the macro, obviously as simple as possible. But this idea works fine, but once the WP column has decimals behind the numbers it no longer works properly and I can't figure it out.

    Friday, October 12, 2012 7:21 PM
  • Jake,

    There must be something missing from your code because the code you posted won't do what you say it does. It does examine each WP value but it compares it with a null string. In your example all rows have a WP value so tn is updated to copy the name value for each row.

    To do what you want the code needs to read WP and compare it to the last value of WP that was read. Then update tn when the value changes.

    If you want to be able to only look at the WP value before the decimal point, you will either need to read is as an integer using the Int function, or if you read it as a string, strip off the characters up to the first decimal point using the Instr and Mid functions.

    John

    Saturday, October 13, 2012 2:40 AM
  • Well originally I had large excel outputs with about 500-1000 activities and some would not include WP's. So I need to continue to compare the WP value to the one before, I am not sure how to do that without comparing it with the offset function.'

    Also I do not need to read the WP before the decimal, I just thought my code was flawed in that it could not read passed the decimal point and was failing to copy down task names. What function should I use to continue to compare the WP so it auto copies the names?

    Monday, October 15, 2012 1:58 PM
  • Jake,

    This description is different from what you described in your original post but this description does track a little better with the macro code you posted.

    As far as comparing something with a previous value, the Offset function provided by Excel will work fine. You just have to manipulate the offset row index so it compares present to previous. The one exception is the first value, since there is no previous value. Normally what I do for the first value is to set a "first value" flag. If the first value flag is true then the code skips around the compare part of the loop. Then the flag is set to false. From then on the present value is compared to the previous value.

    In the loop the present value would be: c.Offset(0,2)

    and the previous value would be: c.Offset(-1,2)

    Does that help?

    John

    Tuesday, October 16, 2012 2:21 AM
  • Can you go a tad further? I understand the comparison idea with past to present but I am unsure how to set the flag to the top value without completely screwing up the code. Could you possibly give me an example of just a loop comparing my columns, I can't seem to get this to work.

    Also is there something that I should add to my code that makes sure that when the number isn't different to keep copying the tn (0,5) value.

    • Edited by Jake683 Monday, October 22, 2012 5:57 PM
    Monday, October 22, 2012 5:18 PM
  • Jake,

    Since you wrote to me separately, we can handle this offline and then publish the results when we're done.

    John

    Monday, October 22, 2012 7:43 PM
  • Jake,

    This macro code should do what you want to organize your Excel spreadsheet.

    Sub JakeExcel()
    Dim xlapp As Excel.Application
    Dim NC As String, tn As String, WP As String
    Dim xlSheet As Worksheet
    Dim c As Range

    Set xlapp = GetObject(, "Excel.Application")
    Set xlSheet = xlapp.ActiveWorkbook.Worksheets(1)
    NC = xlSheet.UsedRange.Rows.Count
    'get starting point work package (WP) and name
    WP = xlSheet.Range("C2")    'Note:".Value" is the default property of a range cell
    tn = xlSheet.Range("F2")
    xlSheet.Range("H2") = tn
    'start comparison loop on third row since there is a header row and row 2 will always
    '   retain the existing name
    For Each c In xlSheet.Range("C3:C" & NC).Cells
        If c <> "" Then
            'if WPs for current line and last line are the same, repeat the name
            If c = c.Offset(-1, 0) Then
                c.Offset(0, 5) = tn
            'if current WP is different or blank, grab the current name
            Else
                tn = c.Offset(0, 3)
                c.Offset(0, 5) = tn
            End If
        'catch repeated blank WPs
        Else: c.Offset(0, 5) = c.Offset(0, 3)
        End If
    Next c
    End Sub

    John

    • Marked as answer by Jake683 Monday, October 29, 2012 4:36 PM
    Sunday, October 28, 2012 10:46 PM