set a number accordingly to the name that it has on Column "E"

Hello there Experts Exchange community needing help on
***when Auto Arrange is click can it exclude all other words that has numbers if it is not under the same name of row column "E" college
***Fixing sorting problem

For example

on college is 5
on business is 2
on accounting is 4

If i click on auto arrange

on college it will start on 6
on business it will start on 3
on accounting it will start on 5

Sub openuserform()
NumberArange.Show
End Sub

Private Sub CommandButton1_Click()
NumberArange.MyNumber.Caption = NumberArange.MyNumber.Caption + 1

End Sub

Private Sub CommandButton2_Click()
NumberArange.MyNumber.Caption = NumberArange.MyNumber.Caption - 1
End Sub



Private Sub CommandButton3_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim rw As Range
Set ws = Worksheets("GENERAL")

For Each rw In ActiveSheet.UsedRange.Rows
If rw.Cells(1, 5) = Label3.Caption And rw.Cells(1, 4) = Label4.Caption Then
rw.Cells(1, 6) = MyNumber.Caption & Right(rw.Cells(1, 6), Len(rw.Cells(1, 6)) - InStr(rw.Cells(1, 6), " ") + 1)
End If
Next rw

MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
End Sub

Private Sub CommandButton4_Click()
Unload Me
End Sub




Private Sub CommandButton5_Click()
Dim rw As Range
Dim mx As Integer
Dim lft As String
mx = 0
For Each rw In ActiveSheet.UsedRange.Rows
If InStr(rw.Cells(1, 6), " ") > 0 Then
lft = Left(rw.Cells(1, 6), InStr(rw.Cells(1, 6), " ") - 1)
If IsNumeric(lft) Then
If Val(lft) > mx Then mx = Val(lft)
End If
End If
Next rw
MyNumber.Caption = mx + 1
End Sub

Private Sub CommandButton6_Click()
    MyNumber.Caption = CommandButton6.Caption
End Sub

Private Sub CommandButton7_Click()
    MyNumber.Caption = CommandButton7.Caption
End Sub

Private Sub MyNumber_Click()

End Sub

Private Sub UserForm_Activate()
If ActiveCell.EntireRow.Cells(1, 6) <> "" Then MyNumber.Caption = Left(ActiveCell.EntireRow.Cells(1, 6), InStr(ActiveCell.EntireRow.Cells(1, 6), " ") - 1)
If ActiveCell.EntireRow.Cells(1, 5) <> "" Then Label3.Caption = ActiveCell.EntireRow.Cells(1, 5)
If ActiveCell.EntireRow.Cells(1, 4) <> "" Then Label4.Caption = ActiveCell.EntireRow.Cells(1, 4)

End Sub

Open in new window

Not-Sorting-as-it-should.png
Not-Sorting-as-it-should-2.png
completed-8-29-22.xlsm
Omar HernandezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Omar HernandezAuthor Commented:
Not-Sorting-as-it-should.pngNot-Sorting-as-it-should-2.png
0
Saqib Husain, SyedEngineerCommented:
Sub openuserform()
NumberArange.Show
End Sub

Private Sub CommandButton1_Click()
NumberArange.MyNumber.Caption = NumberArange.MyNumber.Caption + 1

End Sub

Private Sub CommandButton2_Click()
NumberArange.MyNumber.Caption = NumberArange.MyNumber.Caption - 1
End Sub



Private Sub CommandButton3_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim rw As Range
Set ws = Worksheets("GENERAL")

For Each rw In ActiveSheet.UsedRange.Rows
If rw.Cells(1, 5) = Label3.Caption And rw.Cells(1, 4) = Label4.Caption Then
rw.Cells(1, 6) = MyNumber.Caption & Right(rw.Cells(1, 6), Len(rw.Cells(1, 6)) - InStr(rw.Cells(1, 6), " ") + 1)
End If
Next rw

MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
End Sub

Private Sub CommandButton4_Click()
Unload Me
End Sub




Private Sub CommandButton5_Click()
Dim rw As Range
Dim mx As Integer
Dim lft As String
mx = 0
For Each rw In ActiveSheet.UsedRange.Rows
If InStr(rw.Cells(1, 6), " ") > 0 Then
lft = Left(rw.Cells(1, 6), InStr(rw.Cells(1, 6), " ") - 1)
If IsNumeric(lft) Then
If rw.Cells(1, 5) = ActiveCell.EntireRow.Cells(1, 5) Then
If Val(lft) > mx Then mx = Val(lft)
End If
End If
End If
Next rw
MyNumber.Caption = mx + 1
End Sub

Private Sub CommandButton6_Click()
    MyNumber.Caption = CommandButton6.Caption
End Sub

Private Sub CommandButton7_Click()
    MyNumber.Caption = CommandButton7.Caption
End Sub

Private Sub MyNumber_Click()

End Sub

Private Sub UserForm_Activate()
If ActiveCell.EntireRow.Cells(1, 6) <> "" Then MyNumber.Caption = Left(ActiveCell.EntireRow.Cells(1, 6), InStr(ActiveCell.EntireRow.Cells(1, 6), " ") - 1)
If ActiveCell.EntireRow.Cells(1, 5) <> "" Then Label3.Caption = ActiveCell.EntireRow.Cells(1, 5)
If ActiveCell.EntireRow.Cells(1, 4) <> "" Then Label4.Caption = ActiveCell.EntireRow.Cells(1, 4)

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Omar HernandezAuthor Commented:
Thanks for the quick response saqib, it now gives me error this code is replacing

Auto arrange
hold
zcompleted
 
with

"Auto Arrange --"        "Hold --"     "Zcompleted --"

i think that keeping that " -- " is what is giving me the error instead let's just clear it all off and replace it with description without "--"
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Saqib Husain, SyedEngineerCommented:
Are you working on a different file?

"Auto Arrange --" was not part of what I have given.

The "--" should not be a problem. I just need to know where it is going wrong.
0
Omar HernandezAuthor Commented:
what i mean by saying  Auto Arrange is the actual button that is on the userform that we are currently working on.
0
Omar HernandezAuthor Commented:
the one in color blue
0
Omar HernandezAuthor Commented:
Hello saqib, this code again is doing this
[embed=file 1114732
moving-from-zcompleted-to-hold-2.pngmoving-from-zcompleted-to-hold-then-.pngmoving-from-zcompleted-to-hold.png
0
Omar HernandezAuthor Commented:
and then when i close the userform and try to re-open it again it shows this error
error.png
0
Saqib Husain, SyedEngineerCommented:
Which line is highlighted?
0
Omar HernandezAuthor Commented:
Hello saqib i noticed that the main problem is having "--"
if the userform does not find "--" it will continue to act up.

Might as well let's make it work without recognizing or looking for "--" from any particular cell and if there is "--" delete it to add new information.
0
Omar HernandezAuthor Commented:
in other words

have it working by  not having nothing in the cell and continue to work on those cell that is empty or those that has no "--" on cells. If it does have "--" clear it and add description only without "--" on the cell.

Instead of working with
with "--"     "1--"     "HOLD --"    "2--"    "ZCOMPLETED --"

Have it to work like this
if the cell that is being change has "1 --" clear anything in that cell and make it work "1"
if the cell that is being change has "3 --" clear anything in that cell and make it work "3"
if the cell that is being change has "HOLD --" clear anything in that cell and make it work "HOLD"
if the cell that is being change has "ZCOMPLETED --" clear anything in that cell and make it work "ZCOMPLETED"

hope this is more clear Saqib
0
Omar HernandezAuthor Commented:
Thanks again Saqib for your help again you have made possible to find solution with expedite fashion.
0
Saqib Husain, SyedEngineerCommented:
I don't understand this. You were having problems. How did you overcome them?
0
Omar HernandezAuthor Commented:
sure i just added on the userform buttons that is called Add Task and Sub task another function to add also "--" on the F column so that which ever one did not had a "--" it won't act as if there is an error.

On the auto arrange i noticed that there was no problem but the actual space it had before the number was causing it to not sort correctly, so i just added an additional column so that when ever there is a Task, Sub Task created when using a userform have an additional function. In column "I" if there is a

new Task added add in column I a number "1" with given information of the userform
also for it to add on the following row
same information to D and E row and in column I a letter "z"
new Sub Task when added, add in column "I" a number "2" with given information of the userform.

The first code that you have provided help so much.

new-row.png
0
Saqib Husain, SyedEngineerCommented:
So you do not require further assistance on this?
0
Omar HernandezAuthor Commented:
I will in others, but not on this particular one will message you when i have it posted in the site. Thanks again saqib
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.