Adding New Element To A Collection Propagates Added Value To All Entries
Aug 4, 2014
I'm trying to populate a collection with PossibleAnswers to a Question. Every time I do this the values from the newly added entry get propagated to each entry in the collection (best way I can describe it - see code attachement).
Here's the existing code - including the current debugging code surrounding the add! The function is a Method of a Question object.
[Code] ....
All the Find and FindNext procedures do return the appropriate next value. However, the debug loop produces the following:
[Code] .....
As you can see, something is being added, but it seems to be affecting every single entry.
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?)
this problem is just an follow up question to this thread [Solved] VBA : user input to combobox xla. it supposed to be a follow up question but as per RULES is concerned i have to post a new one "4. Never post a question in the Thread of another member. You MUST ALWAYS start you own New Thread.". my question is how to retain the inputted values upon next opening of the workbook? you see that in this thread everytime the userform initialize the values added are also refresh , but what i want is after i manually input an entry of the combo box on next open the values are saved correctly..
how to make all cells displaying an item from the list source update with new information added to list source.
Example: Cell "A1" on worksheet 2 displays the 3rd slot of information from list source on worsheet 5. I want cell "A1" on worksheet 2 to update when the list source information in the 3rd slot on worksheet 5 is changed.
Notes: The list is set up by naming the columns on Worksheet 5 and is set as a validation list on the other worksheets. So on Worksheet 2 column "A" has the validation
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
I'm using a workbook that has one sheet that pulls data from all the others, displaying a series of rows that summarizes data from each worksheet, each of which is full of data. Its a worksheet that a lot of people use so its kind of tedious to use as of now, because whenever you add a new worksheet of data you have to manually create a row and then manually change all the worksheet references. Is there a way to make rows automatically add and update when a new worksheet is added? I know it would probably have involve some sort of Macro or VBA.
Tom20148 John Malmo Tom20148 Will Malmo Bob20206 Will Malmo Tom20206 Will Paris Bob20206 Rob Rotterdam Bob20207 John Rotterdam Ray20207 John Paris Tom20208 John Malmo Ray20208 Rob London Ray20209 Rob Paris Bob20209 Will Malmo
Is it possible to have excel go through this list and assign each row a number in column 5 based on the names and the IDNumber? Basically, I would want each entry that is identical in name 1, 2 and 3 to be assigned numbers 1, 2, 3, 4, 5 etc based on their IDNumber. So Tom/John/Malmo with the IDNumber 20148 would get the number 1 in column 5, while the next match (Tom/John/Malmo/20208) would get the number 2 in column 5. For each different match of Name 1,2 and 3, I would want the count in column 5 to start at 1. So Bob/Will/Malmo/ 20206 would get number 1, Bob/Will/Malmo/20209 number 2 etc.
I'm trying to do, I already have a macro that takes certain cells in sheets in a workbook and copies to them to individual workbooks that are open.
So for example, I have a workbook with sheets A, B, and C. and then I have a macro that'll copy sheet A to an open workbook D and B to an open workbook E, and so on.
Problems I'm running into = if the workbook isn't open, it crashes, I'd like to have an if then statement where if workbook is not open, it stops?
Additionally, I'd like a for statement because there will be 10 or more sheets in total, so would be nice to have a for statement that changes the variables so For i = 1 to 10 where 1 would be D and 2 would be E and so on so it automatically changes the workbook names as necessary.
As you can see the letters repeat, and the dates are in order. Dates may repeat or be skipped.
I want to know how I retrieve the last date in the list that corresponds to a certain letter. For example, the last date that corresponds to "A" is 10-Jun-12. Similarly for "B", it is 11-Jun-12.
VLOOKUP will retrieve the first day.
In this case corresponding to "A": =VLOOKUP("A",A2:B12,2,FALSE)
Is there any way I can retrieve the last day with a formula?
My main report (Report 1) has part number, serial number, and work order information, among other things I need. Report 2 contains this information as well, but also has a work order item number, which is needed in Report 1. Otherwise, Report 2 has nothing else of interest.
What functions will allow me to get the item number information I want from Report 2 into Report 1?
I have spent a lot of time trying various combinations of VLOOKUP, SUMPRODUCT, IF, etc with no luck. There is probably a very simple (I hope!) thing that I am overlooking.
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
I have the need to create a workbook that will return values based on the entering of an abbreviation. I will use the chemical elements as an example, I wish to have a column formatted so that if 'Au' is entered the cell populates with 'Gold' or if 'Pb' is entered then the cell is populated with 'Lead'
So in my case I have all the abbreviations in one column and all the full descriptions in another (approx 500 different codes/description)
I am trying to parse a section of html into excel but I don't know how to parse into a new row with each pattern rotation. I can do this both in Word and notepad++, but I can't find the same function of replace-with-line-break in excel's find-and-replace box.
Original html extract: "shb":{"landline":"£1","mobile":"£1","text":"8p","mms":"24p"},"af":{"landline":"18p","mobile":"18p","text":"8p","mms":"24p"},"ak":{"landline":"3p","mobile":"3p","text":"8p","mms":"24p"},"al":{"landline":"10p","mobile":"25p","text":"8p","mms":"24p"},"dz":{"landline":"5p","mobile":"27p","text":"8p","mms":"24p"},
Results I need: Capture.PNG
There is only one line as such in the entire page, so I don't need to loop through "blocks" of html codes with multiple lines.
I have had a look around and not sure what would be a good way to tackle this. RegEx (how to do it in excel macro)? HTML tags parsing (but these are not strictly tags)? I need to extract the correct section from page source as well, but I imagine this shouldn't be too hard. The url to the page I am extracting from is [URL] ........
I have a custom range I'll be copying from one sheet (a single entry registration form) that I'm adding to another (an "all entries" sheet) with a button assigned to a macro.
[Code].....
I then loop through the range, copying the cell from Sheet1 to Sheet2.
[Code]....
All works perfect, but I need to manipulate the data a little. If B3 from Sheet1 equals a certain string, I want to manipulate the data From Sheet1 B6 & B7 to paste into Sheet2 Column 1, otherwise paste B3 into Sheet2 Column1.
Again, my loop and everything works if I put B3 into Column1, B6 into Column2, and B7 into Column3, but it doesn't play well with the reporting I want to do later from this sheet.
Data Example
[Code]....
What I'm trying to do is if B3 <> "NEW TEAM" then put B3 (Joe Smith on Sparkles) into the new sheet column1 if B3 = "NEW TEAM" then put "B6 on B7" (Jane Doe on Fluffy) into the new sheet in column1 (where B6 and B7 are the strings, obviously).
I haven't figured out how, in my loop, to access the data in (myCell + 1) and (myCell + 2) while I'm on the first cell (myCell). I could do it by jumping back to the Sheet1("B6") and Sheet1("B7") but I'm trying to write reusable code, and this range might change, so I'm trying to be a little more flexible than hard coding in more cells.
I have 9 named ranges on worksheet Sheet1. I want to print every combination of every non-singular range on worksheet Sheet2. Below is a simplified version of the scenario.
There are three named ranges: Letters, Colors, Animals. Say the below are the entries for each range.
Letters = {A, B, C} Colors = {Red, Blue} Animals = {Dog}I want to print every combination of Letters and Colors but exclude Animals since it only has 1 entry.
Therefore my result would look something like this:
A Red B Red C Red A Blue B Blue C Blue
My thought is to make a multidimensional array GrandArray where GrandArray(1) = Letters and GrandArray(2) = Colors, then recursively go back through every combination and print to Sheet 2. I can set up GrandArray, but stepping through each element is creating mismatch errors.
I'm trying to avoid For loops since my real data has 9 ranges which may or may not be included in the final print.
My rows consist of the following cells, an author(s), an article name, a pubilcation name, a volume number, and a page number. There are thousands.
My first column has multiple names and need to separate the names into separate cells. Some names have a comma or semi-column separating them, some have the word "and" separating them. Most of the names are listed as last name, first name. (Some are not and I will have to deal wtih that later.)
Row 1: Eisenhofer, Jay W., Jordan, Kirk S. Tucker, Marc B."Business Faces Harsh Sentencing Penalties",Delaware Business Review, October 7-13, 1991951
Row 2: Embley, Craig M., Turezyn, Andrew J. and Welch, Edward P."Recent Developments in Delaware Corporate Law",S.A.S.M.& F., May, 19901443
I'm sure is a multiple part process. Is there a formula for placing anything separated by a comma into a new cell, or placing a tab between the names?
Ultimately this data will be placed in database from which to search for a name and article, etc.
I am trying to sqaure every element in a dynamic array and display the result . I donot understand how can I select the value in the cell using VBA?
Dim Y as variant, d() as double, i as long, j as long, rows as double, cols as double Set Y = Application.InputBox("select the matrix: ", Type:=8) Rows = UBound(Y) Cols = UBound(Y, 2) ReDim d(1 To Rows, 1 To Cols) for i = cols d(1,i) = ______==> How do I select the value of element in that particular cell and how do I sqaure it? I know cells(rowindex, columnindex) is used to select a particular cell but If I have a large array it would be difficult to go cell by cell and sqaure it.
how to get the table_array element of vlookup to either obtain details from a worksheet cell, or to enclose a variable (specifically part of the filepath) within the table_array formula. I have a template that gets saved and used in various directories, and I want Vlookup to to dynamically lookup information from a specific file that is also contained within the directory, without having to find and replace the directory name in the vlookup formulas.
I am trying to pass information that is filled by user in a userform into an excel sheet. Let's say a user would click on a control button in a userform and Macro would ask him what value to store for the first variable. If user clicks one more time then Macro would identify that it was a second click and ask what value to set for a second variable. It is easy to do with limited number of variables, but is it possible that the variable which stores a number of clicks would become a number for variable to store the value?
1 click - a1 = .. 2 click - a2 = .. .... n click - an = ..
A friend of mine is playing a computer game, and he has to create 50 different potions to obtain her next goal. She would like to know if it's possible with excel to generate a list of potions based on 7 different elements. Here are the 7 elements : Black orchid, Cactus, Lotus, Pitcher plant, Rose, Tiger lily
Every elements could be repeated to generate one potion.
Therefore, the following combinations are valid :
Cactus - Cactus - Cactus Cactus - Rose - Cactus Cactus - Cactus - Rose Cactus - Rose- Rose
As long as there are no more than 3 elements, it's ok. I'm not looking for the precise coding, I just need to be pushed in the right direction...
I want to obtain from some elements the number of cells it takes to appear: We have for example A,B,C, and D, and they appear in the next order:
1A 2C 3D 4A 5D 6B 7C 8A 9A
What I want to know is how much last in appear each element.
1A1 2C2 3D3 4A3 5D2 6B6 7C5 8A4 9A1
For example, the first “A” last one in appear, but the next element “C” last two in appear. In the forth line again cames the “A”, then are three cells. The “C” was in the cell2, and cames again in the seventh cell, then it takes five cells. In the cells eight and nine are two “A”, then in the cell nine takes one cell in appear again.
My code should get the value of a hidden element from a form that is on a remote website. Then it should display that element's value on my excel worksheet.
One or the other of the 2 lines between the commented out lines should work. Instead the code runs until within a minute after the page has finished loading, and then stops completely without getting the hidden element's value.
Code: Sub work_damit() Dim ieApp As Object Dim URL As String URL = "http://www.craft-e-corner.com/p-2688-new-testament-cricut-cartridge.aspx" Set ieApp = CreateObject("InternetExplorer.Application") ieApp.Navigate URL
I am trying to assign an array element to select a specific cell then assign a value to it. Below is the general code that I am working with. Does anyone know why this is not working?....
For some reason get a type mismatch error on when I try to determine the rth member of summation range. I have highlighted the relevant part of the code in bold. It is strange as I can obtain the address.
If ((All_nurse_names(r, 1) = nurse_name) And (All_status(r, 1) = status)) Then Debug.Print "test " & r & " " & summation_range(r).Address & " " & val(summation_range(r))
total_hours_in_shift = total_hours_in_shift + (summation_range(r)) End If
I have an array with structure, i.e. User Defined Type. Assume the user defined type has two elements: Element1 and Element2 and array name is Array.
So the definition is:
Dim Array(1 To 10) As UserType
and access to elements is
Array(5).Element1
The problem is that I need to pass the whole set of Element1 or Element2 to a function. Should it have been two separate arrays, it would not be a problem. But because of the user defined structure I have no idea how to pass a single element.
I hope there is another solution rather than to use loops. I have many arrays like this with complex structures. I simply can not replicate all of them.
