Solved

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

Posted on 2016-09-01
16
44 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Chocolatey under PowerShell is not working properly 3 45
Hiding column macro 10 28
ADD New Entries 7 16
Help with Excel formula 6 35
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
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.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now