troubleshooting Question

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

Avatar of Omar Hernandez
Omar Hernandez asked on
VB ScriptMicrosoft Excel
16 Comments1 Solution124 ViewsLast Modified:
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()
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

Saqib Husain

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros