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 derfloh 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: *

  • Users: Jean9
  • Order by date
  1. Jean9

    Combo Box Value with VLOOKUP

    The value in E1 is S:\Accounting\Financials\Linked Spreadsheets\[Consolidated 2012 Forecast 1+11.xlsx] After hitting F9 on formula =VLOOKUP(B8,INDIRECT("'" & E1 & "Cons by month'!$B$8:$O$199"),$K$4,0), I see: =VLOOKUP(B8,INDIRECT("'S:\Accounting\Financials\Linked Spreadsheets\[Consolidated 2012...
  2. Jean9

    Combo Box Value with VLOOKUP

    I've tried: =VLOOKUP(B8,"'" & INDIRECT(E1 & "Cons by month'!$B$8:$O$199"),$K$4,0) and =VLOOKUP(B8,INDIRECT("'" & E1 & "Cons by month'!$B$8:$O$199"),$K$4,0) both result in a #REF error and this is while that workbook that the VLOOKUP is referencing is opened. E1 is only going to contain the...
  3. Jean9

    Combo Box Value with VLOOKUP

    Thanks, Skip The use of =VLOOKUP(B8,"'" & INDIRECT(E1) & "Cons by month'!$B$8:$O$199",$K$4,0) in the cell results in a #REF! error even with the file open. However, the user will not have the other workbook open when they use this function. The hardcoded path formula...
  4. Jean9

    Combo Box Value with VLOOKUP

    So, I've created a named combo box control in a MS Excel 2007 workbook containing file names that are stored on another worksheet (DriveFiles) in the same workbook. I've set the combo box cell link to D1 on my worksheet and set E1 =IF(D1="","","S:\Accounting\Financials\Linked Spreadsheets\[" &...
  5. Jean9

    MS Excel - Trap delete range and underlying query warning

    The user doesn't want to have to do anything other than run a macro and the (9) worksheets be populated.
  6. Jean9

    MS Excel - Trap delete range and underlying query warning

    BTW, the users are on MS Excel 2003 for an indeterminate amount of time... @Combo, not sure if that will work for the scenario...but if it seems like it might, then I might need some clarification as to how this can be accomplished @Skip, All worksheets contain data from the same source but...
  7. Jean9

    MS Excel - Trap delete range and underlying query warning

    The users have a worksheet in the workbook that contains thousands of rows of data. Based on particular criteria, if the row of data meets that crtieria they want it "moved" to a sheet containing all the rows meeting that criteria so they can do some further manipulations. They have 9...
  8. Jean9

    MS Excel - Trap delete range and underlying query warning

    @Combo - you're probably right, I wouldn't be able to trap that warning. Maybe I moreso would rather just catch whenever the user chooses a delete action and then only delete the selection and not the queries... @Skip - Your "hide the query sheet and reference it for the users to play with"...
  9. Jean9

    MS Excel - Trap delete range and underlying query warning

    How can one trap the event when the user attempts to delete a range and thereby the underlying query on a worksheet? Currently, the default warning message (The range you deleted is associated with a query that retrieves data from an external source. Do you want to delete the query in addition...
  10. Jean9

    Excel Macro results differ when stepping through code

    I'm actually going to keep the whole adding a query table sub just in case the user inadvertently deletes the query table from the worksheet (I'm proof that this can happen because I accidently deleted the table when trying to delete that total row). The subscript out of range error in the...
  11. Jean9

    Excel Macro results differ when stepping through code

    It was the blasted InitWorsheets routine that was being used to clear up the worksheet before putting the new query table on it...it was deleting the query table from the sheet prior to the macro run with the ChangeDataConnection sub in it (remember it had to be run once with the original sub so...
  12. Jean9

    Excel Macro results differ when stepping through code

    I ran through the original sub in order to establish the connections, then ran the macro with the sub you sent (the sub to remove the queries was commented out so the connections remained). The connections are named generically Connection, Connection1, Connection2, etc.
  13. Jean9

    Excel Macro results differ when stepping through code

    I get the "Error in ChangeDataConnection: 9 Subscript out of range" error when trying to run the following code. The sub errors when it hits the With Sheets(sWS).QueryTables(1) line: Private Sub ChangeDataConnection(sSQL As String, sWS As String) On Error GoTo Error_ChangeDataConnection...
  14. Jean9

    Excel Macro results differ when stepping through code

    Below is the code used to add the Query Table...there might be some unused references and a couple of mis-placed comments...it's not been cleaned up completely.. Private Sub SelectDATA(sSQL As String, sWS As String) On Error GoTo Error_SelectDATA ' Create a connection for each iteration...
  15. Jean9

    Excel Macro results differ when stepping through code

    The subroutine order is like delete any connections from previous runs, clear worksheets ranges A8 through K(last row), create query tables, and then run the totaling sub. The code below is what is used to delete the connections. My thought was that the query defs are stored on the user's...
  16. Jean9

    Excel Macro results differ when stepping through code

    Ummmmm....shrug...that's what THEY want, not me...I'd prefer not to look for the total either. :-) But, that being said, I will say that the data on these tabs is coming from a Query Table soooooo are you saying that I should maybe not try to put the total at the end of the query table? or...
  17. Jean9

    Excel Macro results differ when stepping through code

    Thanks, Geoff and Skip...here's what I changed the code to. Not that it changed my results any...when I put a break in the code and step through, this code works perfectly. When I run the macro without any breaks, the top 8 columns of the worksheets are shifted to the right by two, leaving the...
  18. Jean9

    Excel Macro results differ when stepping through code

    Thanks for pointing me to the right forum and your reply anyhow. Would you by chance have a code sample of indexing a row pointer and using the range property or cells property? I'm pretty much a novice at vba for excel. That was all code to add a Total label and value at the end of whatever...
  19. Jean9

    Excel Macro results differ when stepping through code

    When I step through the code below, the results on the worksheet are as they should be (the total label is in J whatever and the total is in K whatever) but when I run through the macro without any code breaks, the worksheet header rows (in this case range A1:K7) are shifted over to range C1:M7...
  20. Jean9

    Creating a temporary queryable dataset in Excel via VBA

    ...the results to a dataset. Run a select against the dataset like "Select Account, Distribution_Amt, Fund, Dist from rs where Business_Unit like 'TS*' and Account not in ('12345', 23456') and Dist_Amt <> 0 and Freq = 0" where rs is the dataset and the fields are the column headers. Either in...

Part and Inventory Search

Back
Top