none
Macro for auto fill column RRS feed

  • Question

  • Hello together,

    do you know, if there is possibility for a macro that makes the following:

    Search all task names (from top to bottom) for text "Test1".

    If you find and if this task is a summary task then do the following:

    For each task that is after this and not on a lower level write "Test1" in the column "text11"

    Name         Text11

    bla

    bla

    Test1

       bla          Test1

       bla          Test1

            bla     Test1

    bla

    bla

    I really hope, at least one of you understand me and can help me because my VBA knowledge is on a very low level.

    Thanks for you support

    Br

    Tobias

    Wednesday, July 16, 2014 1:19 PM

Answers

  • Tobias,

    This should do it. Note, I only gave it a quick test, so I didn't ring it out thoroughly.

    Also, testing for strings can be a bit unpredictable. For example, if "test1" is inadvertently written as "tst1", or "tast1", or "text1", then the the desired result will not be obtained.

    Option Compare Text
    Sub flowdownText()
    Dim ta As Task
    Dim OL As Integer
    Dim Flag As Boolean
    OL = 50: Flag = False
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If Flag = True And t.OutlineLevel > OL Then
                t.Text11 = "test1"
            ElseIf t.Summary And InStr(1, t.Name, "test1") > 0 Then
                Flag = True
                OL = t.OutlineLevel
            Else
                Flag = False
            End If
        End If
    Next t
    End Sub

    If this answers your question, please mark it as the answer. Otherwise, tell me why it doesn't do what you need and I'll fix it.

    John


    • Edited by John - Project Wednesday, July 16, 2014 2:48 PM code
    • Marked as answer by der_toby Wednesday, July 16, 2014 2:59 PM
    Wednesday, July 16, 2014 2:02 PM

All replies

  • Tobias,

    This should do it. Note, I only gave it a quick test, so I didn't ring it out thoroughly.

    Also, testing for strings can be a bit unpredictable. For example, if "test1" is inadvertently written as "tst1", or "tast1", or "text1", then the the desired result will not be obtained.

    Option Compare Text
    Sub flowdownText()
    Dim ta As Task
    Dim OL As Integer
    Dim Flag As Boolean
    OL = 50: Flag = False
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If Flag = True And t.OutlineLevel > OL Then
                t.Text11 = "test1"
            ElseIf t.Summary And InStr(1, t.Name, "test1") > 0 Then
                Flag = True
                OL = t.OutlineLevel
            Else
                Flag = False
            End If
        End If
    Next t
    End Sub

    If this answers your question, please mark it as the answer. Otherwise, tell me why it doesn't do what you need and I'll fix it.

    John


    • Edited by John - Project Wednesday, July 16, 2014 2:48 PM code
    • Marked as answer by der_toby Wednesday, July 16, 2014 2:59 PM
    Wednesday, July 16, 2014 2:02 PM
  • Would a custom field with formula be a better choice, John?
    Wednesday, July 16, 2014 2:03 PM
    Moderator
  • John,

    thanks in advance!

    I don't know even how it could work with a formula...!?

    Br

    Tobias

    Wednesday, July 16, 2014 2:41 PM
  • Julie,

    No, because you wouldn't be able to test for "test1" at summary level and then flow that down to all lower outline levels.

    John

    Wednesday, July 16, 2014 2:43 PM
  • Hi John,

    I have done a short test, it looks very good - thanks a lot!

    Br

    Tobias

    Wednesday, July 16, 2014 2:59 PM
  • Tobias,

    You're welcome and thanks for the feedback.

    John

    Wednesday, July 16, 2014 5:10 PM
  • Ah, thanks.  I misunderstood the need.
    Wednesday, July 16, 2014 6:13 PM
    Moderator