Change Index Whithin A Collection
Apr 2, 2008
I have several collections of objects in a macro I have created. However I would now like to be able to shift the order of these objects around (ie increase or decrease its index by 1 each time, and shift the other objects around it). For example I have collection1 which contains three objects x,y, and z with indexes 1, 2, and 3 respectively. I now want to give y the index of 1, and so shift x up to 2 so the resultant order is y, x, z. Is there anyway I can do this whithout copying all objects to another collection and recopying back in the desired order?
View 4 Replies
ADVERTISEMENT
Feb 1, 2010
I have created a userform that upon clicking a commandbutton adds a line of 4 textboxes. Everytime the user clicks the commandbutton a new line of textboxes is created. The 4 textboxes in each line are described by a class (hope I am using the terminology correctly). And each instance is saved in a collection. The problem is that I am giving the user the ability to insert a new row in between two existing rows.
When this happens, I need all of the index numbers to re-order so that they are consecutive from the top of the form to the bottom. Example: The user enters 6 rows of data and then realizes that they missed an item that needs to be inserted between rows 3 and 4. Currently the rows are indexed 1 to 6 with 1 being the row at the very top of the form and 6 being the row at the very bottom of the form. If I insert a row in between 3 and 4 I will end up with the index numbers going from top of the form to bottom (1,2,3,7,4,5,6). Is it possible to reorder the index numbers so that the inserted rows index number will be 4 and each row after that will go up by one?
View 9 Replies
View Related
Jul 25, 2012
How to get the index number of a seriescollection within the activechart user has selected .
I am able access the chart selected by the user by using the activechart function, but next i am trying to access the seriescollection selected by the user, in other words the activeseriescollection thing.
Is there any way to access the active seriescollection of an activechart or any other way to access the seriescollecion of a chart selected by a user.
I googled a lot but what i found that we have give the index no. i.e; activechart.seriescollection(1)......
View 2 Replies
View Related
Mar 18, 2008
I have a bar graph with 2 series of data. Anyone knows how I can specify the order in which they appear? code looks similar to the following. The bar graph shows the second series before the first series. How do I reverse the order?
With cht
.ChartType = xlColumnClustered
' first data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With
' second data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With
View 2 Replies
View Related
Dec 5, 2008
This formula locates on sheet 1. Since i'm trying to reference this number 123456167 to the second sheet with the format that looks like 0123456167, I'm trying to change value so both would be the same but I'm keep getting the #N/A.
=INDEX(Sheet2!$B$2:$C$399,MATCH((VALUE(A2)),VALUE(sheet2!$B$2:$B$33),2))
View 2 Replies
View Related
Oct 23, 2009
1Does Excel process worksheets (tabs) in the left-to-right order in which the tabs appear visually? Or in the order of the worksheets' index numbers as they appear in the VBE Project Explorer window? 2Is there a way to renumber or reassign index numbers to specific worksheets?
View 4 Replies
View Related
Jan 16, 2010
I have to do a vlookup in 12 sheets (named ranges)
I use the formula '=vlookup(a1;choose(1;range1;range2; etc);2;0)
In this case I have to change the 'choose index_num' every time to match.
Is it possible to do the lookup without changing the 'choose index_num'?
View 9 Replies
View Related
Oct 25, 2007
Is there a way to automatically change the column index number in the VLOOKUP formula when copying the formula to columns? For example, when I copy a VLOOKUP formula from column A to column B, the cell references will change, but the column index remains the same. I'd like the column index to be increased by 1.
View 2 Replies
View Related
Jul 29, 2014
I have the same report, one a monday snapshot, the other a daily comparison. I would like to index columns B, in both reports, to find the same part number and then reference the horizontal row and highlight increases in columns G thru AD. The comparison report looks identical but the column B numbers may not be in the same location. this is a report to show prosduction requirements and the increases throughout the week.
View 1 Replies
View Related
Mar 16, 2009
If a INDEX,MATCH function returns a zero, how do I get it to use the value in the adjacent cell which is a unique number?
=INDEX(Sheet2!A:C,MATCH(A2,Sheet2!A:A,0),3)
Example:
View 2 Replies
View Related
Jan 6, 2010
I trying to code a SeriesCollection in vba, below is the code I have:
' ActiveChart.Name = "Chart1"
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Name = "='Chart1'!$C$1"
ActiveChart.SeriesCollection(2).Name = "='Chart1'!$D$1"
ActiveChart.SeriesCollection(3).Name = "='Chart1'!$E$1"
ActiveChart.SeriesCollection(4).Name = "='Chart1'!$F$1"
ActiveChart.SeriesCollection(5).Name = "='Chart1'!$G$1"
ActiveChart.SeriesCollection(6).Name = "='Chart1'!$H$1"
ActiveChart.SeriesCollection(7).Name = "='Chart1'!$I$1"
ActiveChart.SeriesCollection(8).Name = "='Chart1'!$J$1"
ActiveChart.SeriesCollection(9).Name = "='Chart1'!$K$1"
ActiveChart.SeriesCollection(10).Name = "='Chart1'!$L$1"
ActiveChart.SeriesCollection(11).Name = "='Chart1'!$M$1"
ActiveChart.SeriesCollection(12).Name = "='Chart1'!$N$1"
However it it giving the error "Object denfined or Appication denfined"
View 9 Replies
View Related
Jun 20, 2006
I am using the following code to determine whether a given range is the linked cell for a Combo Box by looping through the shapes collection.
Function LocateFormControl(OverRange As Range) As Shape
Dim objTemp As Shape
For Each objTemp In OverRange.Parent.Shapes
If Left(objTemp.name, 6) = "Drop D" Then
If WorksheetFunction.Substitute(objTemp.ControlFormat.linkedcell, "$", "") = WorksheetFunction.Substitute(OverRange.Address, "$", "") Then
Set LocateFormControl = objTemp
Exit Function
End If
End If
Next
Set LocateFormControl = Nothing
End Function
However, when I use this code on big worksheets with many other shape objects (such as Comments), the program runs very slowly. Is there a ComboBoxes Collection that I could use to avoid looping through all shapes on the worksheet, or is there a different, faster way to run this code?
View 2 Replies
View Related
Dec 7, 2008
I have a piece of code I am using to important data from a text file. The code works fine but I am having a hard time understanding how defining a Variable "As New Collection" works. I have tried to research on numerous websites dedicated to excel but had no luck finding any further information.
I have executed the code line-by-line over-and-over and believe what the "new collection" does is it creates some type of external database that is hidden within excel (that's just my guess). I have attached a sample file and a text file with dummy data if needed to see how the code is working.
View 4 Replies
View Related
May 26, 2009
Is there a way to remove a value from a collection after I've added it.
I have simplified the code I'm using to highlight what I'm trying to do.
Basically I'm adding 10 keys to a collection and want to remove the last one. There is a reason for this.
View 3 Replies
View Related
Oct 5, 2006
Im workin with a spreadsheet.. At the end of this script you see how i am putting "a" in R11C33, well I want to put the contents of my workbook C3 instead. I just dont know how to do it. After it does that I need it to move on to cell C4 and put it also in r11c33...
View 4 Replies
View Related
Oct 27, 2006
I have a large table (20,000 plus entries) and I need a way to pull out data that matches criteria in buckets.
For example:
April data (column B) that is from 0 to 30 (column C)
April data (column B) that is from 31 to 45 (column C)
... 46 to 60
... 61 to 75
... 75 to infiniti
Then do it again for May, June, July, etc. I have used DCOUNTs to pull this type of data, but it's a bit cumbersome. Any other ways to pull this type of data from the table?
View 3 Replies
View Related
Jan 28, 2007
I am attempting to create a double-entry accounting system and am having trouble with my Transaction class (clsTransaction). As each transaction will consist of at least two entries, I have included a Collection in clsTransaction that will accept Entry objects (clsEntry) as items. The Add method appears to go ahead without a hitch, but when I output the values from the Entries I get the same values for all items in the collection. Here is the code I've been working on.
The Transaction Class
clsTransaction - (Class Module)
Option Explicit
Dim mcolEntries As Collection
Dim msDescription As String
Private Sub Class_Initialize()
Set mcolEntries = New Collection
End Sub
Public Sub Add(eItem As clsEntry)
mcolEntries.Add eItem
End Sub........................
View 3 Replies
View Related
May 2, 2007
I have a userform with three checkboxes on it. In the manuals I have read (and websites I am learning from) I have encountered articles that give me the impression that it is possible to create a collection of almost any object, and then quickly return properties about the collection as a whole. So, I am wondering, is it possible (or even appropriate) to group the checkboxes on this userform into a collection, and then, with a few lines of code, see if any of them are checked? If this is not possible, or efficient, is there another way to do it? I'll admit that three IF statements are not all that hard to enter, but I know the day is coming when I will make userforms with many more checkboxes in them.
View 4 Replies
View Related
May 31, 2007
I have a routine that creates a collection of textboxes to simplify adjusting their positions: ...
View 9 Replies
View Related
Feb 7, 2008
I hope this passes muster. Anyways, I wrote some VBA code to handle an embedded chart in my workbook. I named the chart "Data" but i'm getting some unexpected trouble from VBA. Last time I worked on this set of code, it ran fine. When I try to run it now, it is throwing up an error: "1004 Unable to set name property of series class" The .name function is not working correctly but everything up till that point is working correctly. I have 5 sets of data in this chart also ....
View 9 Replies
View Related
Oct 31, 2009
I have a separate sheet(Coverage.jpg) that records some data which at this stage has to be entered manually.
The data comes from another sheet(officers.jpg), each row is 1 flight and the days are usually separated by a blank or grayed row.
I was wondering is there a formula that will collect the data automatically.
As you will see on Coverage.jpg it is broken down into Number of flights(per day), how many flights were covered by 2 or more officers and how many covered by 1 officer.. and then the graph generates off the data.
Is there a formula or something that will enter the per day data?
I have just added some false data to show you how some things get recorded.
View 14 Replies
View Related
May 27, 2014
Writing up some code, and I have a workbook with many named ranges. Question is simple...
Are named ranges a part of a collection object that I can use?
Looking to do something like:
[Code] .....
How to determine collection objects on my own in the future!
View 2 Replies
View Related
Jul 10, 2014
I'm creating a spreadsheet to collect survey data and how I have it set up is this:
Question 1 is a simple yes/no/maybe question - it uses an ActiveX spin button that the user presses up/down to increase/decrease the total. It looks like this:
Private Sub Yes_SpinUp()
With Range("C3")
.Value = WorksheetFunction.Min(15000, .Value + 1)
End With
End Sub
Private Sub Yes_SpinDown()
With Range("C3")
.Value = WorksheetFunction.Max(0, .Value - 1)
End With
End Sub
Cell C3 is where I keep track of how many people answered yes
The button is called "yes" under the button properties
So basically, I have one privatesub for spinning the value up and down. It works fine and is easily copy-paste-able for other answers, it just takes up a lot of space. I'm wondering if there's code I can write to handle the up/down in one sub. If not, i'll stick with what I have.
View 6 Replies
View Related
Jul 29, 2014
I've created one file: Mappe1_results.xlsm
This file ask in another file "Mappe1_ground.xlsx" for "B" and "N". Now one new sheet is created with the name "month-2014" and shows me which Category (B) is how often referred in one month. After that one chart is created for a better representation. This can be done for every month (which month is selected by the user over the button "Auswertung" in Sheet "Tabelle1").
Now I have a few problems / requirements:
1. If one user is using (maybe) Jan as his selected month, in the sheet "Auswertung" should be one duplicated chart of the Jan Chart., with no other (previous) series. Because Jan has no previous conditions (prev. year).
1.1 If one user is using another month (maybe Feb), in the sheet "Auswertung" should be one chart with both series of Jan and Feb and so one (for the other months). So that in "Auswertung" the chart is one comparison over the months. Only Feb/Mar/Apr/May/Jun/Jul/Aug/Sept/Oct/Nov/Dec have one prev. month.
For example: If one user write "Apr" into the inputbox and "Mar" is in the sheet existing, so in the chart of "Auswertung" should be April and March shown.
Some functions are set in my macros, but the problem is that the results are not equal between the sheet "Auswertung" and maybe "Jan-2014" or "Feb-2014". Because in "Auswertung" we need called all Categories (they can be found in "Referenz" - A).
All what I want is in "Auswertung" one chart with all present categories of "Referenz" - A and the series of the created sheets by the user.
2. The next problem is, every created sheet has one legend "Anzahl im ..." - this legend of every series should be shown in "Auswertung", too. So that we know which color is for which month, u know.
View 2 Replies
View Related
May 19, 2013
I have a VBA module in Access that extracts data and copies to an Excel spreadsheet. Every time I run the code additional data is extracted and displayed on a chart together with previous data. When I enter values to the series collection the first axis be it X or Y is entered correctly but the second axis produces error 1004 'unable to set XValues property of the series class' If I swap the lines of code around the error switches to 'Values' (y axis)
wchart.SeriesCollection(NoCurves).Values = wsheet.Cells(9, 3 + (3 * (NoCurves - 1)))
wchart.SeriesCollection(NoCurves).XValues = wsheet.Cells(8, 3 + (3 * (NoCurves - 1)))wsheet.Cells(a, b))
wchart.SeriesCollection(NoCurves).Name = wsheet.Range(Cells(6, 2 + (3 * (NoCurves - 1))), Cells(7, 2 + (3 * (NoCurves - 1))))
View 2 Replies
View Related
Jul 30, 2013
I set out this morning to count duplicates in an array and report a succinct list.... so I went down the route of using a collection with keys to do this.
1. Is there a better way? Else
2. split my collection values? I'm a bit stuck.
I have a series of values in a collection like this
: Item 2 : "Spam|01/07/2013|1" : Variant/String
: Item 4 : "Chips|01/07/2013|2" : Variant/String
So I have 3 key pieces of data delimited by a pipe sign -
Product | Date | Number of Occurrences
Item 4 is of interest to me because it occurs twice.
How I can turn those collection values into something I can work with, else another approach to sum rows in an array which are duplicates (if you only look at 2 columns).
View 6 Replies
View Related
Aug 30, 2013
I have a graph with something like 40 series that are being plotted. For one, I create non-standard data labels using VBA. In that code, I refer to ChartObjects(1).Chart.SeriesCollection(34). At some point I deleted a series that preceded it in the "select data" list and the code crashed. Took a lot of digging to learn that SeriesCollection(34) had become SeriesCollection(33).
To avoid this in the future, is there a way to refer to the SeriesCollection by a unique name rather than a sequential number?
View 4 Replies
View Related
Nov 15, 2013
Code:
Private garage_ As Collection
Public Property Get garage() As Collection: Set garage = garage_: End Property
Public Property Set garage(ByVal value As Collection): Set garage_ = value: End Property
[Code]...
I have the above piece of code within a "Person class" in order to add a vehicle to a person's garage. I was wondering if there is a better way to structure and/or execute this? (Perhaps not using a collection? or my adding to the collection directly?)
View 5 Replies
View Related
May 8, 2007
i have 4 excel files which need to be opened and add into workbooks collection. how am i suppose to do it. The reason i'm doing because i want don't want to seperate the user's open workbooks and my macro open's workbook. e.g if the user opened 3 excel files, when my macro run, it will open 4 excel files which will be used for my macro processing. The total open files now will be 7 excel files. therefore, i want to have a seperate workbooks collection to manage my 4 files.
below is my psedure code.
1. open 4 excel files
2. add all those 4 excel file into workbooks collection which is sperate from the user opened excel file
3. interate these 4 excel file by extracting the data and put into new workbook.
what i need is just a peice of code where i can add the workbook that i have open into collection workbooks
View 9 Replies
View Related
Dec 15, 2007
I will try to keep this as short as possible. I have a huge amount of stringed data from dry kiln runs all well organized. I want to make something that will look at all this data (or at lease the ones that I specify) and give me a prediction of what would happen if I was to make a change somewhere in the schedule. I have alot more info if needed.
View 10 Replies
View Related
