PDA

View Full Version : Solved: Interesting Auto-Number Problem VBA



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)

Bob Phillips
05-28-2009, 04:26 AM
Sub RenumberSubtasks()
Dim LastRow As Long
Dim i As Long
'Number Column A with 1.00, 1.01, 1.02, 2.00, 2.01, 2.02 and then put 2.01 into Cell "C1"

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

If Not Int(Cells(i, "A").Value) = Cells(i, "A").Value Then

Cells(i, "A").Value = Cells(i - 1, "A").Value + 0.01
End If
Next i
End Sub

maxhayden
05-28-2009, 05:35 AM
Thank you very much. It works very well.

I am quite new to this so I don't quite understand how the Int() works.

Surely Cells(i, "A") will always be equal to Cells(i, "A") ??

If Not Int(Cells(i, "A").Value) = Cells(i, "A").Value Then

Could you explain?

Bob Phillips
05-28-2009, 05:36 AM
Not the integer value it won't.

Int(7.5) is 7.

maxhayden
05-28-2009, 05:39 AM
I think I understand now...

Its a - If the value is not an integer..

Bob Phillips
05-28-2009, 05:41 AM
Yes, effectively.

maxhayden
05-28-2009, 05:46 AM
That leads me on to the next question then...

If I was to adapt it so that the number I wanted to change was the Main Task.... e.g///

1.00
1.01
1.02
3.00
3.01

To change the "3.00" to "2.00"...?

Maybe something like this?


If Cells(i, "A").Value - Cells(i-1,"A").Value > 1 Then

Cells(i, "A") = WorksheetFunction.RoundUp(Cells(i-1, "A"), 0)

maxhayden
05-28-2009, 05:50 AM
Woops... That almost works... but it converts the 3.01, 3.02... all to 2.00s.......

I think I need to put an If Int(...) in there

maxhayden
05-28-2009, 05:55 AM
I found it...

This works


If Int(Cells(j, "A").Value) - Cells(j - 1, "A").Value > 1 Then

Cells(j, "A") = WorksheetFunction.RoundUp(Cells(j - 1, "A"), 0)

Bob Phillips
05-28-2009, 08:59 AM
You could just use




If Not Int(Cells(i, "A").Value) = Cells(i, "A").Value Then

Cells(i, "A").Value = Cells(i - 1, "A").Value + 0.01
Else

Cells(i, "A").Value = Int(Cells(i - 1, "A").Value )+ 1
End If

maxhayden
05-28-2009, 09:03 AM
the "+0.01" part doesn't work for the Main Tasks.

Eg:

3.01
3.02
5.00

--> 3.02+0.01 = 3.03 when I want 4.00 (hence the "RoundUp")

Bob Phillips
05-28-2009, 09:06 AM
The main tasks fail the Int test so they should just get 1 added.