Solved

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

Posted on 2016-09-01
16
54 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

856 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