Solved

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

Posted on 2016-10-07
20
28 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
  • 11
  • 9
20 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
Just to be sure we are working with the same data and code, please post your workbook as it is now.
0
 

Author Comment

by:Omar Hernandez
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
Also, what is "DIVIDER"?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Omar Hernandez
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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 45

Expert Comment

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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

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,…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

744 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

13 Experts available now in Live!

Get 1:1 Help Now