Solved

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

Posted on 2016-09-01
16
55 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

713 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