maxhayden
05-28-2009, 03:12 AM
Hello all,
I've come across a small problem and wonder if there is a funtion in VBA to solve it:
In Column A I have a numbering system as follows:
1.00 - Main Task (MT)
1.01 - Subtask (st)
1.02 - st
2.00 - MT
2.01 - st
3.00 - MT
And so on.....
I have a User Form where the user can type the number of the task into a field and the macro will:
- Search for the task
- Determine whether it is a MT or st
- If it is a MT, it will delete all corresponding st's
- If it is a st, it will delete just that st.
The problem I have is re-numbering the tasks after the deletion has been made. I'm sure there is a way of doing it but I'm quite stuck.
Here's some test code for a spread sheet using the Subtask part of the same concept:
Sub Subtask_Deleter_Test()
'Number Column A with 1.00, 1.01, 1.02, 2.00, 2.01, 2.02 and then put 2.01 into Cell "C1"
For i = 1 To 10
If Cells(i, 1) = Cells(1, 3) Then
If Cells(i, 1) - Application.WorksheetFunction.RoundDown(Cells(1, 3), 0) <> 0 Then
Rows(i).Delete
End If
End If
Next i
End Sub
FYI - The RoundDown function is used to confirm that it is a subtask. (Eg. 1.45 - 1.00 {rd} <> 0, Subtask) (Where 2.00 - 2.00 {rd} = 0, Main Task)
I've come across a small problem and wonder if there is a funtion in VBA to solve it:
In Column A I have a numbering system as follows:
1.00 - Main Task (MT)
1.01 - Subtask (st)
1.02 - st
2.00 - MT
2.01 - st
3.00 - MT
And so on.....
I have a User Form where the user can type the number of the task into a field and the macro will:
- Search for the task
- Determine whether it is a MT or st
- If it is a MT, it will delete all corresponding st's
- If it is a st, it will delete just that st.
The problem I have is re-numbering the tasks after the deletion has been made. I'm sure there is a way of doing it but I'm quite stuck.
Here's some test code for a spread sheet using the Subtask part of the same concept:
Sub Subtask_Deleter_Test()
'Number Column A with 1.00, 1.01, 1.02, 2.00, 2.01, 2.02 and then put 2.01 into Cell "C1"
For i = 1 To 10
If Cells(i, 1) = Cells(1, 3) Then
If Cells(i, 1) - Application.WorksheetFunction.RoundDown(Cells(1, 3), 0) <> 0 Then
Rows(i).Delete
End If
End If
Next i
End Sub
FYI - The RoundDown function is used to confirm that it is a subtask. (Eg. 1.45 - 1.00 {rd} <> 0, Subtask) (Where 2.00 - 2.00 {rd} = 0, Main Task)