Return the value in the task above the active cell in MS Project using VBA

Question

I am familiar with VBA in Excel and for the life of me I can't work out how to return the value that is above the activecell in MS Project using VBA. In Excel I would just use something like activecell.offset(-1,0).value.

In the code below I have (incorrectly) used OFFSET so would need something that could replace that code in order to make my macro work.

The code is trying to add a new task summary when the value in the Text4 column changes for the next task (and indents otherwise).

Thank you in advance!

Sub Add_Task_Summaries()
'Add a Summary Task when the text in the Learning Path column changes and 
indent otherwise
Dim T As Task
Dim LP As String
Dim RowNo As Long
Dim TU As Integer

For Each T In ActiveProject.Tasks

If T.Text4 <> "" And T.Summary = False Then
    If T.Text4 = T.Text4.Offset(-1, 0) Then 'INCORRECT SYNTAX
        T.OutlineIndent
    ElseIf T.Text4 <> T.Text4.Offset(-1, 0) Then 'INCORRECT SYNTAX
        LP = T.Text4
        T.Add Name:=LP, before:=ActiveSelection.Tasks
    End If
End If
Next T

End Sub

Rachel Hettinger Rachel Hettinger · Accepted Answer · 2018-10-18T17:01:07

You can select cells in MS Project by using various Select methods of Application object (e.g. SelectCellDown, SelectCellRight, SelectBeginning). However you could also use a method like this:

Sub Add_Task_Summaries()
    'Add a Summary Task when the text in the Learning Path column changes
    'and indent otherwise

    Dim T As Task
    Dim S As Task
    Dim LP As String

    For Each T In ActiveProject.Tasks
        If T.Text4 > "" And Not T.Summary Then
            If LP <> T.Text4 Then
                LP = T.Text4
                Set S = ActiveProject.Tasks.Add(LP & " - Summary", T.ID)
                S.OutlineLevel = T.OutlineLevel
            End If
            T.OutlineIndent
        End If
    Next T

End Sub

Return the value in the task above the active cell in MS Project using VBA

1 Answers