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 RepliesI'm getting the following error:
"Object library invalid or contains references to object definitions that could not be found"
I wasn't getting that error last night and I'm not sure what I may have done to cause this error.
It seems to be cause by code running on one sheet of my workbook, but I'm not really sure about that. I'm still a bit of a novice at VBA.
I'm using Excel 2002 SP3 and I'm running MS XP Home as my OS.
Do you have any ideas what can cause this error and/or how to trace down the offending objects/code?
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"
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?
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.
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.
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?
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........................
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 RelatedI have a routine that creates a collection of textboxes to simplify adjusting their positions: ...
View 9 Replies View RelatedI 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 RelatedI 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.
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!
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.
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.
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?
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))))
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).
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?
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?)
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
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 Relatedi need a command to remove (delete) a member of a collection by its key. Something like: SomeXCollection.Remove 2213acv (where 2213acv is the key property of certain SomeXCollection member object) but this syntax (i found in Excel Help), doesn't work to me.
View 3 Replies View RelatedI came across this expression going through an Excel VBA code. Can someone plz explain what the argument (-1,1) points to ?
View 9 Replies View RelatedI'm looking for a way to name lines in a pivot chart instead of default numbering eg ActiveChart.SeriesCollection(1). This in order to attach variable lines to y-axis no 2 no matter which selection is made in the pivot.
View 3 Replies View RelatedI 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 View RelatedI am using a bar graph and would like conditional formatting for a series collection. So if the series collection is greater than 30%, the single entry (not the whole series) would turn red. I have attached an image of what I currently have. There is a data validation list at the top which tells the graph what series to use, so the data is always changing.
I hope this all makes sense. Basically, if an entry is above 30%, I want the single entry in the series to turn red.
I'm trying to determine how many different excel files are open, but my VB program runs outside of excel (in a program called Pulse).
Here's the code so far:
set ExcelApp=CreateObject("Excel.Application")
ExcelApp.Visible=True
For Each Workbook in ExcelApp.Workbooks
MsgBox(Workbook.Name)
Next
Msgbox(ExcelApp.Workbooks.Count)
The only message box I get is the last line of code, and it says "0" even though I have multiple excel workbooks open.
I like using New Collection method for make Uniq items as below
(using excel and his vbe only)
'Option Explicit
Option Base 1
Sub UniqDataMaker()
Columns("D:M").ClearContents
Dim i As Double, j As Double
Dim rng As Range
Set rng = Range("AllData")
Dim STRrng
STRrng = rng.Value
Dim adds As New Collection
On Error Resume Next
For i = 1 To UBound(STRrng)
adds.Add "'" & CStr(STRrng(i, 1)), "'" & CStr(STRrng(i, 1))
Next
On my think...
if occurs error number 457 then
resume next and add only new items only.
Just yesterday
When occurs error number 457 then the coding is always stop with msg error 457
I checked and re-install office, and re-boot.
but stoped that line with error 457
with this file or coding
other PC never occurs error
I use Excel version 2000,xp,2003 but never finish job. -_-;;
adds.Add "'" & CStr(STRrng(i, 1)), "'" & CStr(STRrng(i, 1))
-> error.
adds.Add CStr(STRrng(i, 1)), CStr(STRrng(i, 1))
-> error.
I did my best, but I give up;;
some one know why stop error 457?
I have a UserForm function which accepts as a string the name of a TextBox control on the form. The function returns a reference to the named TextBox control (or Nothing if the control does not exist). I have the function header defined this way:
Public Function TextBox(byval strName as String) as TextBox
The function returns a reference to the TextBox control like this:
set TextBox = Me.Controls.Item(strName)
(I know, the .Item is not required.)
The code which calls the above function first declares a TextBox object with this Dim statement:
Dim txtTextBox as TextBox
The routine then goes on to call the TextBox function like this:
set txtTextBox = TextBox(strTextBoxName)
When run, the above statement genrates a Type Mismatch error. I'm confused, because if I 'TypeName()' the TextBox function's returned value, it's "TextBox,".
I thought I could work around this problem by changing the TextBox function's return value's data type to Variant, but that produced the same result.
The only "solution" I've come up with is changing the TextBox function's return value's type (and any reference to the functions' return value) to Control.
I expected that the Controls object would behave similar to a Collection object in that it's items can be different types (TextBox, Label, CommandButton, ListBox, etc.) and no Type Mistmatch erros occur so long as the type of the item returned matches the type of the variable referencing that item. But is seems that Controls requires that any reference to one of its items must be type Control, not the actual type of the item returned.
I'd prefer not to use Control data rypes in my applcation, as that would require additional code in all of my subs and functions to ensure that any Control object passed to it is the correct type of control (TextBox, Label, etc.).
Can anyone explain what's going on here? Why shouldn't I be able to assign a TextBox type variable to Controls.Item("xyz"), so long as the item returned by Controls.Item("xyz") is type TextBox?