I have to use VisibleSlicerItemsList as it is an external data source (so cannot use ActiveWorkbook.SlicerCaches(Slicer Name).SlicerItems(Slicer Valuel).Selected = True/false)
Now, when user chooses 2,3 or more countries, they will be in cells A2, A3, A4... etc.
So, if the user selected 2 countries I would need to run a following code:
I've inherited a workbook that has about two dozen or so pivots spread over a few worksheets that source data from an analysis services cube.
There are multiple slicers attached to the various pivot tables and charts
Some of the reports (worksheets) have a business rule that certain filters need to be selected in specific combinations for the data that's returned to make sense.
eg the fact table holding the measures has a billmonth and processmonth that's linked to role playing date dimensions. For the report to reconcile the data correctly both the billmonth and processmonth need to be set to the same value. So if I select 2014-03 on the billmonth slicer, I need to set the processmonth slicer to 2014-03 as well. And there's another pivot on the same worksheet that's linked to a different fact table that's at the year grain, and for that bit to make sense it should be set to 2014.
My task is to simplify this by propagating the billmonth value to the processmonth and the billyear, but I haven't played with vba in about 7 or so years, so I'm very out of practise.
How do I use vba to monitor a slicer for changes? and if it does change how to set another slicer to a dynamic .Value? It can be assumed that the value will always exist in the downstream slicers, if it doesn't the user has bigger problems than an excel error.
I've recorded the macro of me selecting the same date on both slicers but it doesn't give me much to go on
I' have two slicers that each control two pivot tables. The slicers both are for the same field ("Department") but I guess the pivot table structure differences don't allow me to simply have one control all four tables.
Since both slicers contain the exact same options from the same field on the same origin table, I would like to problematically ensure that when department "A" is selected on slicer1, it is also selected on slicer2. When departments "A, B, & C" are selected on slicer1, the same are selected on slicer2. When filters are cleared... you get the picture.
I have a activex multi-selection list box (on a worksheet). It has two columns, referenced from two columns on the worksheet (state abbreviation, state code) AA7:AB58. I'm using the following code below to transfer selections to cell A1.
The goal here is to select the worksheets in one list box and transfer it to another. Multiple Selection is allowed. I am having 2 question i am struggling with.
Question 1 - When passing the sheet names from List Box 1 to List Box 2 (using Add button) i would like the selected items of List Box 1 to be deleted after transfer. I thought i could access this using the command below but it does not work properly -> error message.
Question 2 - When the user is done the ListBox 2 need to be stored. When I save then close my workbook and then reopen it, i would like to be able to access the selection that the user has made before closing the worksheet. How can I store the content of ListBox 2 to achieve such a result. I guess i could use a use a hidden Worksheet where i could store the selection into some cells but i would like to know if there is a pure VB solution.
I have a workbook that has multiple functions. One of these function is that when the user select a key word from a drop down validation list, a VB code transfer all information from that row from Column A to column W to the next available row on another tab.
VB: If Not Intersect(Target, Range("X:X")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False
If LCase(Trim(Target.Value)) = "yes" Then
[Code] ....
Is there a possibility to transfer this information into another workbook. Instead of transferring the info to a tab name "Archives" to transfer in another workbook. This workbook will only have one tab called "Archives".
This will minimize the information within my Initial workbook in project.
The path in the new workbook where the information should be transfer should be in:
"C:Documents and Settings"username"My DocumentsMy Data Sources" to an excel workbook that will be named Archives.
I know that the "Archives" workbook need to open then close after the information has been inserted...
Is there a limit to the size of an array that can be transposed in Excel '97? The following works with an array size of 1000 by 4 but not 2000 by 4
Dim arrDetail() As Variant Dim i As Long, Dim counter As Long counter = 1 For i = 1 To 2000 If a certain condition Then Redim Preserve arrDetail(1 To 4, 1 To counter) arrDetail(1, i) = "string" & Rnd(i) arrDetail(2, i) = i arrDetail(3, i) = Cells(i, 1).Address arrDetail(4, i) = IIf(i Mod 2 = 0, True, False) counter = counter + 1 End If Next i Range(Cells(1,1), Cells(counter,4)) = Application.Transpose(arrDetail)...........
i write a micro. Just transfer the array into the excel table, but i do not know to do it. The question is that: Once the array has been filled,ask the user for a location to output it. when this has been obtained and verified then output the table. Provide the row and colomn headings and a suitable title fot the table.
so, ask the user for a location to output, what code can be used. if use set range=application.inutbox(prompt,,,,,8), the user can choose the range, but how transfer the array into the choosed range? and the range choosed is not equal to the array, how to slow it?
trying to speed up run time of legacy app, learned that setting a range = an array is a better strategy than manipulating cells one by one, no problem with this approach on cell values but getting errors trying same approach with comments as follows:
1) Select/Copy rows 1 thru 35 of column C from "Start" tab. 2) Special Paste/transpose them in row 2 of "Finish" tab (1st 35 columns in row 2 should fill up). 3) Go back to "Start" tab, move exactly three rows down from bottom of previous selection in column C and copy the next 35 rows (rows 38 thru 72 in column C). 4) Again return to "Finish" tab, and special paste/transpose in next row down (row 3 in this case). 5) Continue to repeat this action until all rows of data in column C have been copied and transposed/pasted from "Start" tab to "Finish" tab. Variable number of rows on "Start" tab.
I have a form that an administrator will open to search for items needing approved (ie. status is "Submitted" and they'll be going in to confirmit). The datasource is a table. For these purposes I'll arbitrarily call it Table1 for ease. There are multiple columns in the table, and I'll set the column width to "0" for those I don't want displayed, so we'll call the columns to be displayed Col2, Col3, and Col22. There are two dependents, because Col2 is the Division, of which there are 3 (Central, East, and West). The administator will select their Division and the associated ListBox will display results for all locations having a item status of "Submitted". Col3 will be the branch name, and Col25 with be a total $ amount of the order. Cbo1 (ComboBox1) will house the Private Sub Change() for the macro after a division has been selected.
Thus far I have thought of using a loop such as:
Code: Private Sub ComboBox1_Change() For n = 1 to ws.ListObjects(1).DataBodyRange.Rows.CountIf ws.ListObjects(1).DataBodyRange.Cells(n,2) = Me.ComboBox1.Value and ws.ListObjects(1).DataBodyRange.Cells(n,25) Like "*Submitted*" ThenMe.ListBox1.AddItem = If ws.ListObjects(1).DataBodyRange.Rows(n).Value2End IfNext n End Sub
I've got a mismatch in the the Value2 type for the .AddItem. Not sure how else to approach though.
when is it appropriate to us arr(1 to 10) Vs. For i = 1 to 10. I know this may sound like a silly question and expose my ignorance - but I suppose there is no other way to learn :|
I am using a VLOOKUP formula, but when I drag it down it doesn't keep the same array selection. Is there a way so that I can drag it and it relates to the cell to the left, but the array selected stays the same. I don't really want to type the same thig out for every cell as there are 6000. I am using: =VLOOKUP(D2,Working!A2:C23,2,FALSE) and want D2 to change accordingly but for working!A2:C23 to stay the same.
I faced a problem to attach this small WB as an XLS
I'm looking for some way to some the red cells without looping.
In this example the array was filled with A1:A10 values. In the real situation the array gets its values from other source than a Worksheet Range.
As you can see I manged to transport the Array Values to F1:F10 and from here I could calculate the sum of F3:F8 but I do not want to use any helper columns.
I have created some code of which this is an extract
Dim i As Variant
i = Array(37, 38, 41, 42)
For Each i In i
'Some Code Next i
the routine works fine when the i variable is hard coded, but once the above is included it fails (Error 10 This array is fixed or temporarily locked).
I've built a simple inventory tracking system, and decided a reporting feature would be nice. There are four categories that are entered when inventory is removed...Date, Employee Name, Item Description, Location, and quantity.
Four my reporting purposes I'm only concerned with Date, Employee name and Item Description.
I've been able to write code that does what I want using a multiple Cases and a For loop once the case is identified. However, the more data there is the longer this takes...so I decided to stretch myself and try my hand at arrays (first time really working with arrays), but I'm having trouble figuring out exactly what I need to do.
Here is what I think the steps need to be.
1. Store my data (the categories above) which are located in the Check Out sheet 2. Go through the arrayed data to find exact matches based on my search criteria (here is where the Cases come in) 3. Pull out only that data and write the information to a "Report" Sheet 4. Export that sheet to PDF (this part I already have)
Below is a copy of what my current "working" code looks like (I should mention that the search selections are made from a userform this is what the Cases are deciphering between which ones are blank etc...), also most of my variables are instantiated as Public variables within a Public_Variables module also below.
I have a For loop, its an array with 5 file names in it. The loop checks for the files in the path, and if the file is there it places the file in the corresponding sheet in the workbook. If it is not, I get the option to browse for the file. Originally all 5 files were required, however now the requirements changed and only 4 are required the last file "byband.csv" is not required. So they want the option a message box telling the user the file is not required they can browse or keep going.
I worked out the second part but only by taking "byband.csv" out of the loop and writing a separate procedure for it, I wondered if in my original procedure I could isolate "byband" and if not found go to another part of the procedure. Basically, I needed to do the following:
If the file in the array byBand.csv is not found, Then:
vmbProceed = MsgBox(strFifthImportFile & strMessage, vbYesNo + vbQuestion, strTitle) If vmbProceed = vbNo Then Exit Sub Else
go to Line 1 in the procedure below.
Code:
Sub import_Employee_Data()
'This is Step 1 when the frmDataImportSplash is activated. 'This procedure imports the byEmployee.csv sheet. The procedure checks if the file is 'in the same directory as the template. If the file is not there, a browser window appears to allow the user 'to browse for the missing file. A series of message boxes guide the user through the process and 'verifies that the user picked the right file. The user can cancel the import at any time.
Dim strPath As String Dim strFirstImportFile As Variant
how to fill array with cells form selections and loop my macro for each cell of that array.
Code: 'this is macro I need to execute for each cell in my selection. Here I threat each cell from selection as solo selection.
Sub SELECTION_CELL_COUNT_SINGLE() Dim z As Integer, q As Integer, X As Boolean, I As Integer, txt As String Dim tmp As String, J As Integer, K As Integer Dim sStrip As String
I would like to use array v to store all the results of the for loop u...How can Ido it?
Dim myRange As Range Dim AnsRange1 As Integer Dim AnsRange As Range Set myRange = Application.InputBox(Prompt:="Select row to insert 10 rows below", Type:=8) AnsRange1 = myRange.Row Dim u As Integer Dim v As Integer Dim var() As Single v = 0 For u = 23 To 24022 Step 9 var(v) = u Next u If Not (AnsRange1 = v) Then MsgBox AnsRange1 Else Range(AnsRange1 & ":" & AnsRange1 + 9).Insert Shift:=xlDown End If End If
trying to loop through an array, with each loop calculating a formula (VLOOKUP) for one cell based on the values of a cell in another sheet.
Not sure but I think it has something to do with looking up a text value while the loop returns a value.
Private Sub ExpandFormula() 'calculates Counttarget formula in COUNTTARGETS sheet across required range Dim CellsDown As Long, CellsAcross As Integer Dim i As Long, j As Integer Dim TempArray() As String Dim TheRange As Range Dim CurrentValue As String
'get the dimensions CellsDown = Worksheets("SEPARATE").Range("D9").Value CellsAcross = Worksheets("SEPARATE").Range("E9").Value
'redimension temporary array Redim TempArray(1 To CellsDown, 1 To CellsAcross) .....................
Is it possible to use an array without looping through it? I have code that has two loops: One that is just for the array and the other that loops through the data. It would be nice if I could get it down to one loop, but I'm not sure if it's possible. For example, if you have Dim varArea As Variant
varArea = Array(1,2,4,6,8,12)
For x = LBound(varArea) To UBound(VarArea) Do While Not C Is Nothing Code here Is there a way to advance to the next item in the array without using the loop.
I am having a problem calling buttons. I have an array with button names and another array with their caption texts and I would like to call them one by one in a for loop:
For i = 1 To UBound(button_array) Sheets("Statistic").Shades(button_array(i)).Caption = button_text(i) Next i
But the only way I seem to be able to call them is by their set names, like:
I am trying to fill an array with only a few values within a loop (when XspacingA is 0.5, add the value of the Line to the array) and keep getting the error "Subscript out of range".
I have created a macro that loops through and creates an array of the visible sheets. Now I would like for it to print each of those sheets out. I think my main mistake is in my declaration of the array type, since I have not worked with arrays much before.
how to make the following code operable. Currently when I run it I'm getting a "Run-time error (9): Subscript out of range" error.
I'm hoping to automate some pivot table refreshes. I've got a dozen pivot tables on a sheet all with different numbers. I'm hoping I can write a code that loops through the pivot table names and runs the refresh. Here is the code as it is right now. I've essentially copied, pasted, and changed the name of the pivot table for the refresh.
I would like to create an array in vba (22,21,20,19,18...) that renames the PivotTable and runs the code.
sub Refresh () Dim pt4 As PivotTable Dim Field4 As PivotField Dim NewCat4 As String
Im using slicers of a pivot table which has been generated from a sheet containing around 50 rows of data - not a lot of information. I deleted the information, then started re added information. I then refreshed the pivot table which worked but the slicer still contains information that I input a week ago. I deleted the slicer an cretaed a new one but again, it still contains data from a week ago even thought the pivot doesn't. How to make the slicer refresh to show contains of the pivot?
