Solved

Trying to fix NUMBER ARRANGE Button to load faster and more efficiently.

Posted on 2016-10-07
20
49 Views
Last Modified: 2016-10-08
Hello there Experts Exchange community, trying to have this file that when i update/use the NUMBER ARRANGE to update information in a faster load time.

https://www.youtube.com/watch?v=sXNkQPr12t8&feature=youtu.be

Or

Copy and paste these words on the search bar that is on the Youtube Page.
When clicking on the Number Arrange loads for a min or so
28975047a.xlsm
0
Comment
Question by:Omar Hernandez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 9
20 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41834420
Please let me know what it should do including what columns it looks at. I'd also like to see a before and after picture of what it should look like after select, say, cell E33.
0
 

Author Comment

by:Omar Hernandez
ID: 41834479
When i select any cell on row 33, when i click on the (NUMBER ARRANGE) button it will show on the pop up box (FOLDER: "COLLEGE") only make new updates on what ever the word is next to (FOLDER) in this case it will be "COLLEGE". Try these step and let me know if you understand if not i will create a video friend.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41834504
Just to be sure we are working with the same data and code, please post your workbook as it is now.
0
Technology Partners: 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!

 

Author Comment

by:Omar Hernandez
ID: 41834517
yes we are working on the same one, it is posted on the top of this page will posted again, no prob.
28975047a.xlsm
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41834565
I don't understand what
only make new updates on what ever the word is next to (FOLDER) in this case it will be "COLLEGE"
means.
0
 

Author Comment

by:Omar Hernandez
ID: 41834571
Check out the new video Martin Liss, it is in the lengthy side but is because this userform has other function so that you may see and understand other function that has to do with this userform.

***( When looking for A word please include both of these words. Word "DIVIDER" and also the word next to "FOLDER" Only) Hopefully this will help.

https://www.youtube.com/watch?v=hcG5BBZll1M&feature=youtu.be

or

Youtube / Search Bar

Search only the word that is next to FOLDER
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41834579
OK, using your workbook when I click NUMBER ARRANGE button I see the same result as you. I don't know why my workbook didn't return the same results.

When the NUMBER ARRANGE button is clicked, #VALUE! errors show up in column J because the NUMBER ARRANGE button changes what's in column J. Should it change what's there? In words, please tell me exactly what should happen at the end step 5 in the following actions:
1) A cell in row 33 is selected
2) NUMBER ARRANGE is clicked
3) Add 1 is clicked
4) Generate is clicked
5) Close is clicked

I know that only COLLEGE items are shown, but does anything happen to the data?
0
 

Author Comment

by:Omar Hernandez
ID: 41834591
Column "J" is the question that you have answer that has to do with the number of "Q's" = date

Every time after the NUMBER ARRANGE button is click when i click on the lower right side close button do the following.

Example - When i click on the NUMBER ARRANGE have it that when i close the pop up box only to search the word next to FOLDER + DIVIDER only if there are other words on there ignore.If word next to folder is Business, it will only look for Business + DIVIDER

Example - When i click on the NUMBER ARRANGE have it that when i close the pop up box only to search the word next to FOLDER + DIVIDER only if there are other words on there ignore. If word next to folder is COLLEGE, it will only look for COLLEGE + DIVIDER

Example - When i click on the NUMBER ARRANGE have it that when i close the pop up box only to search the word next to FOLDER + DIVIDER only if there are other words on there ignore. If word next to folder is Excel, it will only look for Excel + DIVIDER

Hope this help my friend.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41834593
I'm sorry but you didn't answer my question; at the end of step 5 does anything happen to the data?

Column "J" is the question that you have answer that has to do with the number of "Q's" = date
I know that, but the code for the NUMBER ARRANGE button changes what's in column "J"! I assume it should not do that, but it's in the code. Did you at some point add or delete columns to the left of column J? The code that changes column J is attempting to replace "*" with blanks. The only column that I know of that has "*" is column F. Should I change the code to refer to F rather than J?
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41834597
Also, what is "DIVIDER"?
0
 

Author Comment

by:Omar Hernandez
ID: 41834665
Hey martin liss hopefully you will see what was the issue on this video, this was something that did not cross our mind specially mines.

Please on youtube copy and paste the following on search bar

copy and paste with button cells to others

or

https://www.youtube.com/watch?v=wSB4fjTgY2U&feature=youtu.be

Updated Attachment is here
28975047a.xlsm
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41834991
Please verify that when the Close button is clicked you want to copy all the "1" from column F to column J.

Questions:
1) Column F contains "1 *". Do you want "1 *" copied or just "1"?
2) Does it matter what cell is selected?
3) If there happens to be a formula in J, copying "1" or "1 *" will replace that formula. Is that OK?
0
 

Author Comment

by:Omar Hernandez
ID: 41835144
1) Column F contains "1 *". Do you want "1 *" copied or just "1"?
***  "1" will be great.

2) Does it matter what cell is selected?
*** No it does not matter what cell it select, because if any cell selected on the desire row, on the NUMBER ARRANGE button it will always be displaying the number that is on the "F" column from the selected row.

3)If there happens to be a formula in J, copying "1" or "1 *" will replace that formula. Is that OK?
always replace new information on the "J" column The way it use to work is as follow
***Always search for all the "1" on the "G" column
    *on those "1" found on "G" column, go to that same row copy whats on "F" and paste on "J"

For example
On "G5" there is a #1 copy from that row what's on "F" (1) and paste on "J" (1)
On "G33" there is a #1 copy from that row what's on "F" (2) and paste on "J" (2)
On "G41" there is a #1 copy from that row what's on "F" (3) and paste on "J" (3)
On "G47" there is a #1 copy from that row what's on "F" (4) and paste on "J" (4)
On "G52" there is a #1 copy from that row what's on "F" (5) and paste on "J" (5)
On "G75" there is a #1 copy from that row  what's on "F" (ZCOMPLETED) and paste on "J" (ZCOMPLETED)

And so on.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41835233
So I don't always copy all the 1's, I sometimes may copy Just the 2's,  if the row selected has a 2?
0
 

Author Comment

by:Omar Hernandez
ID: 41835251
Q) So I don't always copy all the 1's, I sometimes may copy Just the 2's,  if the row selected has a 2?
if found a 1 on "G" column the information that is being copy from the "F" column will always be a different #. The only word that may be repeated is ZCOMPLETED, ONHOLD

Breaking it up by step would be as follow.

*Search for all 1 on the  "G" column.
   - On those that 1 was found on "G" column, search and copy information those on  "F" column and paste it on "J"
0
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41835269
Try this. I eliminated the flicker and added a msgbox so you can tell that something happened when you click "Close"
28975135.xlsm
0
 

Author Comment

by:Omar Hernandez
ID: 41835272
Works great love it, noticed that the Double click and highlight get's disable when i click on the close button located on the NUMBER ARRANGE BOX. There after
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41835290
Replace CommandButton4_Click with this, or just add line 79.

Private Sub CommandButton4_Click()
'
' refresh Macro
'

'
    Dim lngLastRow As Long
    Dim lngRow As Long

    '******* 28975135 Start *******
    Closing = True
    Application.ScreenUpdating = False
    '******* 28975135 End *********
    
    ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
        Add Key:=Range("Table134[[#All],[DONE]]"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
        Add Key:=Range("Table134[[#All],[TIMER]]"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
        Add Key:=Range("Table134[[#All],[TASK]]"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
        Add Key:=Range("Table134[[#All],[ORGANIZER]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    With ActiveWorkbook.Worksheets("GENERAL")
        lngLastRow = .Range("J1048576").End(xlUp).Row
        For lngRow = 1 To lngLastRow
            If .Cells(lngRow, "G") = 1 Then
                .Cells(lngRow, "J") = Val(.Cells(lngRow, "F"))
            End If
            If InStr(1, .Cells(lngRow, "J").Formula, "=Organizer2") > 0 Then
                ' Trigger the Worksheet_Change event for column 2
                .Cells(lngRow, "B") = .Cells(lngRow, "B")
            End If
        Next
    End With
    
    '******* 28975135 Start *******
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Closing = False
    '******* 28975135 End *********
    
    Unload Me
    '******* 28975135 Start *******
    MsgBox ActiveSheet.Shapes("Bevel 10").TextFrame.Characters.Text & " is completed"
    '******* 28975135 End *********
End Sub

Open in new window

0
 

Author Closing Comment

by:Omar Hernandez
ID: 41835293
Once again Martin thanks a mil really appreciate it. :) nice code and faster than what i was expecting the load time to being.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41835295
Can we do the change now that won't allow an end date to be before a start date?
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

738 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