How to enter and change the connection string and command text of a Querytable in VBA?
I know that for pivot table we can create a variable for the command text and connection then use activeworkbooks.pivotcache.connection/commandtext equal those variables. Is there a way to do the same thing with Querytable?
I tried to do something like
activeworkbooks.worksheets(1).querytables(1).connection, but it did not work. Also, I have two different connections for two different querytables within the same workbook.
See also questions close to this topic
Microsoft Excel: Macro/VB script Code to Perform multiple “Contains” Text Filter
I have a list of ~10K values in a column and I need to select based on "contains" text filter and copy it in another sheet. for simplicity lets assume the list as below :
HONDA City HONDA CRV Mitsubishi ASX Honda City X Class Mitsubishi Outlander Mercedes Benz Jaguar some version HONDA Accord and so on...
I need to copy all values containing "HONDA", "Mitsubishi" and "Jaguar" in another sheet.
Is it possible Store literal VBA Variables into an Array?
I have code that has a large series of pre-computed range variables. I'm looking to store those variables into an array where I can apply a loop. I don't want to have to re-declare some variable inside the loop as it is already declared elsewhere. However, I also do not want to duplicate my loop as the code is almost exactly the same.
Dim myarray(1) as variant, I as long, cell as range 'Some code that predetermines the needed variables. Type Range myarray(0) = Rng1 myarray(1) = Rng2 for i = 0 to 1 for each cell in myarray(i) 'code to loop through cells next cell next i
When I get to
for each cell in myarray(i)I get
Run-time error '424': Object required. Perhaps I am misunderstanding the purpose/use of an array. I can do this with other methods, but would require the re-declaration within the loop. As this bit of code is part of a much larger set of code and could mess up down the line, this is not the preferred option.
When I search this site or other sites, I find the phrase "Store variables into an array," but these are referring to pulling static values from a variable source such as a text file or some type reference to values stored in a sheet. I'm looking to store a literal variable of type range into it and be able to use it in a loop. Am I misunderstanding an array's declaration, calling, or perhaps it's general use?
SUMIFS to sum data between custom date range
I am building a dashboard to summarize purchase data. I have a worksheet for purchase data, there is a reference tables worksheet with the start and end dates for fiscal months, with the associated fiscal year, and fiscal month (summarized below), and the dashboard worksheet.
Start Date | End Date | Month | Year 10/1/17 | 10/30/17 | October | 2018 10/31/17 | 11/1/17 | November | 2018
On the dashboard there is a table of data formatted as below
| Beginning Budget | Total Purchases | Remaining Budget | October | $XXXXXXXXX.00 | $XXXXXX.00 | $XXXXXXXXXX.00 | November | $XXXXXXXXX.00 | $XXXXXX.00 | $XXXXXXXXXX.00 | December | $XXXXXXXXX.00 | $XXXXXX.00 | $XXXXXXXXXX.00 | January | $XXXXXXXXX.00 | $XXXXXX.00 | $XXXXXXXXXX.00 |
The formula I am looking to enter would go in the Total purchases, and would sum all purchases made in the fiscal month on the right, based on the table for fiscal months, while meeting several other criteria, which I have been able to complete with the
SUMIFSformula. I am just not sure how to handle the date range portion as they do not follow the calendar months even remotely mostly, and the table for the dates is formatted the way it is as several other portions utilize its current format.
Any help is greatly appreciated!
Loop works once, then "Run-time error '424': Object Required"
Very new to coding, and VBA is my first foray into the subject. Took on a project at work and thought "hey, maybe I could macro my way out of this".
Need to go through a column and if StatCell doesn't equal DateCell, I want it to delete StatCell and the three cells to the right. Repeat in that row until we get StatCell=DateCell, then move down to the next row and repeat.
Here's the code I've got so far:
Dim StatRange As Range Dim StatCell As Range Dim DateCell As Range Set DateCell = Range("B1") Set StatRange = Range("B4:B500") For Each StatCell In StatRange Do While StatCell.Value <> DateCell.Value Range(StatCell.Offset(0, 0), StatCell.Offset(0, 3)).Select Selection.Delete Shift:=xlToLeft Loop Next StatCell
It works at first, and does the first deletion as expected, but only once. I then get error 424 in this line: Do While StatCell.Value <> DateCell.Value
Is the fact that I just deleted StatCell and shifted the row over what's causing this error? How do I get around that?
I have a feeling that my error is basic and obvious, but as I said, I'm very new to coding and probably missed a lot of basic lessons. Any help is appreciated!