Solved

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

Posted on 2016-09-01
16
47 Views
Last Modified: 2016-09-01
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
0
Comment
Question by:Omar Hernandez
  • 11
  • 5
16 Comments
 

Author Comment

by:Omar Hernandez
ID: 41779360
Not-Sorting-as-it-should.pngNot-Sorting-as-it-should-2.png
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 41779393
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
 

Author Comment

by:Omar Hernandez
ID: 41779418
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41779432
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
 

Author Comment

by:Omar Hernandez
ID: 41779438
what i mean by saying  Auto Arrange is the actual button that is on the userform that we are currently working on.
0
 

Author Comment

by:Omar Hernandez
ID: 41779439
the one in color blue
0
 

Author Comment

by:Omar Hernandez
ID: 41780131
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
 

Author Comment

by:Omar Hernandez
ID: 41780140
and then when i close the userform and try to re-open it again it shows this error
error.png
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41780175
Which line is highlighted?
0
 

Author Comment

by:Omar Hernandez
ID: 41780195
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
 

Author Comment

by:Omar Hernandez
ID: 41780246
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
 

Author Closing Comment

by:Omar Hernandez
ID: 41780887
Thanks again Saqib for your help again you have made possible to find solution with expedite fashion.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41780899
I don't understand this. You were having problems. How did you overcome them?
0
 

Author Comment

by:Omar Hernandez
ID: 41780918
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41780920
So you do not require further assistance on this?
0
 

Author Comment

by:Omar Hernandez
ID: 41780923
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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question