Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. Queryman

    Extremely slow VBA code

    Thanks Geoff and Zack for your replies. Michael
  2. Queryman

    Extremely slow VBA code

    Thanks Geoff, I changed to code per your suggestiopn. It still takes 20 minutes to complete the macro. Sub cleanup() Application.ScreenUpdating = False Sheet2.Visible = True Sheet2.Range("A1:F10000").ClearContents Sheet1.Range("A1").AutoFilter Field:=2, Criteria1:="Notes:"...
  3. Queryman

    Extremely slow VBA code

    I have some code that does the job, but takes over twenty minutes to run. The code works on 10,000 lines of data on an excel tab and cleans it up (deleting some columns and also all teh blank rows etc and then moving it to a new tab), I do have application.screenupdating set to false and I have...
  4. Queryman

    Sumproduct using multiple colums for criteria and summing

    I think I solved it. I need to do the following SUMPRODUCT((Sheet1!$A$2:$A$11=Sheet1!A15)*(Sheet1!$B$2:$B$11)+(Sheet1!$C$2:$C$11=Sheet1!A15)*(Sheet1!$D$2:$D$11)) So I look at col A and sum the values in B, then to that I add by looking at col C and summing col E etc.. Michael
  5. Queryman

    Sumproduct using multiple colums for criteria and summing

    Thanks Blue, I tried your suggestion, the formula does not give an error, but gives a result of zero. Did you get it to work properly? Michael
  6. Queryman

    Sumproduct using multiple colums for criteria and summing

    ...below does not work, any assistance will be much appreciated. if any other way to do this other than sumproduct exists, please let me know. SUMPRODUCT(((Jan!A4:A37,Jan!C4:C37,,Jan!E4:E37,,Jan!G4:G37,Jan!I4:I37)='2006 Summary'!A5)*(Jan!B4:B37,Jan!D4:D37,Jan!F4:F37,Jan!H4:H37,Jan!J4:J37))...
  7. Queryman

    VBA BeforeClose problem

    Did try SendKeys "%{F4}", True , did not work Michael
  8. Queryman

    VBA BeforeClose problem

    The code that opens the form is inside each workbooks beforeclose event, changing that line inside Thisworkbook beforeclose event seems complicated. The change I have is a one time change, wher I am adding some data to each workbook, saving and closing it. In teh future, I would want that...
  9. Queryman

    VBA BeforeClose problem

    The form only appears on the beforeclose because I use it to show some info for the users. The fowm is not loaded before that, so it cannot be unloaded or killed. It is part of the beforeclose event to show the form. Form is modal. If I did not have so many workbooks to open and change, I...
  10. Queryman

    VBA BeforeClose problem

    I did, the form is called frmTBP. I tried kill frmTBP, unload frmTBP, it does not seem to make a difference Michael
  11. Queryman

    VBA BeforeClose problem

    I have several workbooks that are formatted the same, but have different data. I have a userform that shows up for two seconds when the workbooks are closed. I am now changing the workbooks using only code and want to save and close them, but that triggers the showing of the user form. Since I...
  12. Queryman

    Excel Ranking Macro

    You are a genius, works perfectly!!!!! Michael
  13. Queryman

    Excel Ranking Macro

    I have managed to confuse everyone including myself, I apologize. I am ranking territories with a region by their sales and also ranking territories within a division by their sales Your formula works, but it is giving me ranks I don't quite follow. Following is my ranks arrived for row 2...
  14. Queryman

    Excel Ranking Macro

    COL A Region B Division C Territory AH Total Parts AI Total Sales $ AJ Region $ Rank AK Division $ Rank Michael
  15. Queryman

    Excel Ranking Macro

    I must not understand how to use this because I get #N/A. For region rank formula I have =RANK(AI2,$AI$2:$AI$351) For division rank formula I had before change =RANK(AI2,$AI$2:$AI$10) after change =RANK(AI2,INDIRECT("B" & MATCH($AI2,$AI$2:$AI$351,0) & ":B" & MATCH($AI2,$AI$2:$AI$351,1)+1))...
  16. Queryman

    Excel Ranking Macro

    Thanks, i will try this Michael
  17. Queryman

    Excel Ranking Macro

    It's easy to create a rank formula and copy down, but I have to do two ranks, one for territories in a region which say for example is 500 entries and they have 50 divisions, I need to create a seperate rank for each territory within a division and each division might have five to ten...
  18. Queryman

    Excel Ranking Macro

    I often have to create ranks in Excel and it's time consuming when there are multiple rows of ranks based on Regional performance versus Divisional performance. I was wondering whether anyone has some code that they could share that would help in this process. I am thinking that if the code will...
  19. Queryman

    Copying Column Selected into code

    Tnanks, I also found this thread thread707-938499 Michael
  20. Queryman

    Copying Column Selected into code

    Thanks for pointing that out. Related question: I am using the code to do the following: I am trying to split a worksheet by a columns value and creating sheets out of each one, I have an option to keep them as separate tabs in the same workbook, or save each tab to a different workbook. The...

Part and Inventory Search

Back
Top