Formula for Moving Cell Contents

[terrapinie]

This question stems from formatting a MS project file in Excel.
I have copied and pasted the schedule into an excel file with the
'Outline Level' column, so I can auto filter for each level of
subtasks and move them over one column to preserve the indented format
of Project. I have created a macro to do this, however I had to move
each row or small group of rows over individually because excel won't
allow me to move a group of cells with 'hidden' rows in between them.
So, when I want to import the schedule into excel again, if I have
added or removed any tasks, this macro will not work correctly.
I would like to create a formula (or macro) to do something like this:
IF(J1=2,move cell contents to D1,IF(J1=3,move cell contents to
E1,IF(J1=4,move cell contents to F1, etc.
with J1 being my 'outline level' column, C1 being the original task
column, and D1, E1, F1, etc. being the inserted columns for indenting
subtasks.

Can anyone shed any light on this? [and I have a feeling I'm going to
hear that it's impossible... :-( ]

Thanks a ton,
Laurie

 

[Trevor]

It is ceatinly not impossible.

Do you want to physically move the information, or is it okay to leave the
task names in C1, but also copy them to D, E, & F? If it's okay to just
dopy them, then all you need to do is put the following formula in column D:
=IF(J1=2,C1,"")
in column E
=IF(J1=3,C1,"")
and in column F
=IF(J1=4,C1,"")

If you want to MOVE the task name from column C to the other columns, then
you're better off with a macro. Create a new macro, and use the following
code. Replace the 1 with the number of the first row where a task appears
(probably row 2). This macro will work it's way down column C until it
comes across a blank cell.

Dim ThisRow As Integer
Dim DestinationColumn As String

ThisRow = 1
Range("C" & ThisRow).Select
Do While Not (IsEmpty(ActiveCell))
Select Case Range("J" & ThisRow)
Case 2
DestinationColumn = "D"
Case 3
DestinationColumn = "E"
Case 4
DestinationColumn = "F"
Case Else
DestinationColumn = "C"
End Select
Selection.Cut Destination:=Range(DestinationColumn & ThisRow)
ThisRow = ThisRow + 1
Range("C" & ThisRow).Select
Loop

If you have a non-contiguous list of tasks, just rerun the macro using
different starting numbers. Or, instead of ThisRow = 1, you can use the
following code:
ThisRow = ActiveCell.Row
And then be sure to make your selection (place your cursor) in the top row
of a block of tasks before running the macro.

 

[Earl Kiosterud]

Laurie,

Try this:

Sub Indent()
Dim Roww As Long ' Row index
Dim MoveColumn As Integer ' column to which to move
Roww = 2 ' starting row
Do
MoveColumn = Cells(Roww, 10) + 2
Cells(Roww, 3).Cut Destination:=Cells(Roww, MoveColumn)
Roww = Roww + 1
Loop While Cells(Roww, 3) <> ""
End Sub