Dictionary Value Not Being Updated?
Aug 13, 2014
I have 2 dictionaries; both have a "location" and the "frequency of occurence". The 2nd dictionary locations are related to the first, but are named differently, so I have a range of cells I'm using to define the equivalent locations, ie:
Dict. 2 Location----Dict. 1 Location
Wrapper------------Packaging
Robot----------------Robot Arm
Robot----------------Robot Base
etc..
(It's not a direct 1 for 1)
The program is meant to take both dictionaries, convert the 2nd dictionary keys to the equivalent dictionary 1 keys (based on the cell range data provided) and then combine the values associated with that location and store them in D_Master. D_Master is a copy of my dictionary 1, in which I am also trying to add the values from dictionary 2.
I started by writing the location definitions within the program, i.e.:
[Code]....
which works, but there are several hundred definitions and it becomes less robust, whereas a user could type in a definition within the range of cells and the code below could take care of the rest.
View 5 Replies
ADVERTISEMENT
Jan 10, 2014
I have two dictionary objects populated with various keys and items - Dict1 and Dict 2. I've read that the items of a dictionary object can be quite varied, even another dictionary! I know how to add items to keys if those items are strings say, but how to add an entire dictionary (Dict2) as the items of another dictionary (Dict1)?
View 5 Replies
View Related
Oct 31, 2009
i have to reverse a very big wordlist containing four coulmns. Column A words, Column B Transcription, Column C Grammar and Column D Meanings. Now i would like to make the meanigs (seperator is ";") to words and words to meaning in another new worksheet added by a macro. For example English-French would then become French-English wordlist.
If the original worksheets name is "x" then a new worksheet should be added with name "Re-x". This new sheet should then contain the new wordlist. For example:
View 12 Replies
View Related
Feb 23, 2010
When we should use scripting dictionary?
Any website recommendation on the net with a good explanation about this, related to use it in excel?
View 9 Replies
View Related
Sep 7, 2006
The comparison between 2 sheets is made in a Class and the Result of the comparison is to be coded in a Module. How the result has to look like is attached as result.xls
I think it has to beginn like this
Sub DisplayResult(ByVal RESULT As Dictionary)
.
.
.
.
End Sub
View 3 Replies
View Related
Nov 9, 2012
I have data in three columns A, B and C. In column A I have values like:
A
1. AOL
2. BA
3. HDP
and now if cell A1 has value AOL then in cell B1 only possible values are:
DD or DP or MP or MR or RE or TP or TT
if cell B1 has value DP then in cell C1 only possible values are:
KO or MA or RS or SA or SE or UM
if cell B1 has value MR then in cell C1 only possible values are:
KO or MA or RS or SA
View 6 Replies
View Related
Jul 29, 2014
I wish to make a transposition using dictionary. I wanted to take a script jindon, but I have difficulties to adapt.
VB:
Option Explicit
Sub test()
Dim a, i As Long, ii As Long, w, rng As Range, cpt As Byte
a = Cells(1).CurrentRegion.Value
[Code] .....
View 9 Replies
View Related
Jul 9, 2014
I have dictionary defined as series of keys and let's say two values:
name1,val1A,val1B
name2,val2A,val2B
...and so on
I would like to define named range from "name" column allowing user to select desired name from combo on another sheet. This is easy
But after that I would like to get val1 and val2 for selected name and show them with some calculation; For example to construct two columns like this: <nameX_selected_from_combo>, (<val1X>+<val2X>)/2
All the problem is how to select values from the same row as name selected in range.
View 2 Replies
View Related
Dec 23, 2007
I have two wordlists (dictionaries) in two sheets and want to make third dictionary out of them in a third sheet.
The first dictionary is Iranian-German and the second one is German-English. Now the macro is supposed to make a third dictionary Iranian-German-English. The new wordlist should contain all words from first list, even if some of them are not translated through macro.
attached excel file to understand the problem better.
Excel file
View 14 Replies
View Related
Oct 31, 2009
I know the Dictionary Object is confined to the Microsoft Word Object structure though can it be used in Excel. If not, is there a suitable replacement.
View 3 Replies
View Related
Nov 12, 2012
In my code below I have verified that the key that I am passing to the item is valid with a debug statement however the item is not being returned.
These two lines are not returning anything. The idea is to pass in the key and get the value.
Code:
Debug.Print dict.Item(Products.Cells(Target.Row, Target.Column - 4))
Products.Cells(Target.Row, Target.Column + 1).Value = dict.Item(Products.Cells(Target.Row, Target.Column - 4))
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ingredients As Worksheet
Dim Products As Worksheet
Set Ingredients = Sheets("Ingredients")
Set Products = Sheets("Products")
' Culculate total cost
[Code] .........
View 1 Replies
View Related
Aug 25, 2008
given a key can i get the position within the dictionary that key/item was found?
so if i go
dic.add "skratch", "more"
dic.add "records", "dude"
how would i obtain the position of "records" within dic? is it possible ?
View 9 Replies
View Related
Jul 13, 2013
I have a VBA code which checks the value of two cells in "Sheet2", and when these values are in the fourth column of "Sheet4", then dictionary is saved and after that VBA delete Entire Rows in "Sheet4" which consists these values.
Please find the code below:
VB:
Sub dictionary1()
Application.ScreenUpdating = False
Dim dico1 As Scripting.Dictionary
Set dico1 = New Dictionary
[Code] .....
Now I wonder how to add more conditions like for example: "Delete all rows in "Sheet4" which have for instance in the 6th column text "Hello" AND which have in the 7th column value greater than 10". How can I change the existing code?
This is of course just an example, but the underlying question is how can I add to these dictionaries more conditions. Of course, we can still use "Sheet2" to add some value which we want dictionary to store.
I want to use for this only dictionaries because spreadsheet is large and filtering doesn't work at all...
View 7 Replies
View Related
Oct 17, 2013
I have 2 tables, one on the left and another one the right, I want to check existance of ID and date in table in left, if match then plant the details as below. I manage to do the matching, but the result isn't correct.
Matching.jpg
Download workbook here [URL]
VB:
Sub vk()
Dim err As Worksheet: Set err = Sheets("Sheet1")
Dim a As Object: Set a = CreateObject("Scripting.dictionary")
[Code].....
View 9 Replies
View Related
Feb 5, 2014
I am working on the last step in a very long project, how to filter my data based on keys in a dictionary.
The data I am working with (in the ActiveSheet) has a list of names (in column 6). I want to add these names as keys in the dictionary and the corresponding location (in column 7) as the items. Then, I want to filter the data by each key in the dictionary, copy the filtered data, and paste it into another workbook. I have included the code I have so far. It seems to work exactly how I want as long as I'm not using the dictionary.
View 2 Replies
View Related
Mar 17, 2014
create a macro that will generate 2 words from the dictionary. The first word needs to start with the letter B and the second word must start with the letter T.
View 1 Replies
View Related
Mar 9, 2009
I have created a dictionary with technical terminology. It is in excel format with about 10,000 entries. The format of my dictionary ("Sheet1") is in three columns:
<vocabulary term> <pronunciation> <definition>
Now, I want to create vocabulary lists using my dictionary. The newly typed up vocabulary list (which only has the term, but not pronunciation or definition), will be copy and pasted into "Sheet2".
The "Sheet2" typed up vocabulary list is in the format:
<vocabulary term 1>
<vocabulary term 2>
etc...
I want to run a macro or script to dictionary terms (including pronunciation and definition) from "Sheet1" and put them into my vocabulary list "Sheet2".
The desired output list in "Sheet2" is: .....
View 11 Replies
View Related
Nov 10, 2009
The code works well. Though there are a few that that i dont understand.
The same range in used for the listbox and the combobox. Though i cannot send the Variable Rng through the scripting dictonary it seams that this function is looking at the range in a diffrent manner to a standard range.
so you see i have another variable datarng for use in the function though if i use set to set the variable as the range it wont like that either.
View 8 Replies
View Related
Oct 1, 2013
I'm trying to build a dictionary of abstract words. It seems that all abstract words are built from less than 30 other indefinable words. I need a macro that can generate column H. I would prefer a macro because ultimately i will probably have a database of about 300 words. What I need is a macro that will go down column G, see the word "after" then find that word in column e and the corresponding number in column f, then write that number in column H. Every word that occurs in a row in column g, should ultimately be converted into a number in column h.
View 6 Replies
View Related
Dec 31, 2008
I have data in a dictionary object and need to load it into a two column listbox. The VBA help says you can load data into a multicolumn listbox from a 2D array. So my question is how can I extract the data from a dictionary object directly into a 2D array.
The dictionary object stores data in key and item pairs. So the 2D array would have one dimension for the keys and the other for the items. I want to do this without having to extract the items and keys into separate 1D arrays and then loop through them to build the 2D array.
View 9 Replies
View Related
Aug 25, 2009
I m creating a dictionary from a set of data that changes, then matching the value to another column and pasting both rows on a new sheet.
I have never worked with dictionaries and I was not able to find much on-line tutorials (help) for this.
I am not even sure if dictionary would be the right tool.
Here's what I am trying to do:
with tow sets of data -- Set 1: columns A-N and Set 2: columns P-AE
-- Go down column M until the last row with data and see if there are any matching values for each value in column M on column AB.
-- For those where a match is found: Copy rows from set 1 column A-N to Sheet "Run" and below copy the rows with the matching value from set 2 columns P-AB.
-- If no match is found, then just copy rows from set 1 on the bottom
-- if there are rows left in set 2 with values not found in set 1, then copy those rows to the bottom of the data in sheet "Run".
View 9 Replies
View Related
Jun 27, 2012
I am using a dictionary object from the MS Scripting Runtime library to store a series of arrays and perform operations on the array cells as necessary. There is a for loop to go through the process of creating all of these entries. My issue is that when using the .exists property, it is returning a True even before the item has been added. Closer debugging indicates that the key is being added to the dictionary at the beginning of the for loop, even though no .add command is used and will not be used until the end of the loop. The result is that the values in the arrays do not totalize as intended.
Code for the dictionary build has been included below. I have attached the Excel file which contains the appropriate module which calls that function
VB:
Option Explicit[ATTACH]46705[/ATTACH]
Function DictAppTable(Loop_Range As Integer, Key_Range As Range, Dim_1 As Range, Dim_2 As Range)
Dim DictTable As Dictionary
Dim AppElement(0 To 0, 0 To 1) As Variant
Dim iD As Integer
Dim strAppID As String
[Code].... the previous post, as the pared down excel file which I posted had a slight error which prevented it from running
View 2 Replies
View Related
Dec 5, 2013
I already did this with excel formulas and it works pretty good. The problem is that when I work with lots (tons) of data, it takes minutes for the formulas to update and do all the needed calculations.
I have been trying to solve this problem using a dictionary object, since it should work faster. However; I don't even know how to start.
My problem (see uploaded Workbook) is this:
I need to calculate daily profits from different departments. For that I need to subtract daily expenses from daily revenues. Unfortunately, the order of appearance of the departments is random. At the end it should look something like this for every day and department:
[Profit].[Dep?] = [TotalRevenue].[Dep?] - [TotalExpenses].[Dep?]
I really don't know what to do with...
Dictionary Object supports below methods :
Add - add key-item in dictionary object
Exists - Check the existence of key in a dictionary object
Items - Get the collection of all items.
Keys - Get the collection of all keys.
Remove - remove particular key-item
RemoveAll - Remove all keys-items
Dictionary Object supports Properties
Count - gets the count of keys
Item - sets or gets the item
Key - sets the key in dictionary object
View 9 Replies
View Related
Mar 30, 2012
What I'm trying to do:
1) I have data that has a header row of 6-10 values (created, action, type, id, ...). The rows are unique records.
Click here to see the data
2) I originally wanted to read the data into one array (DataArray) and the header into another array (KeyArray). Then I would add additional unique information about each record (additional columns of info) into the DataArray.
3) I stumbled upon Dictionaries as a way of storing key/item pairs, which I thought could apply to each record since the headers are all unique and would be a way of storing key/items without needing to:
a) Know the ordering (if I used arrays I had to know the upper bound of the array to insert a column
b) Look up which what position in the array mapped to which column header
c) Resize the array every time I wanted to insert a new column of information on all the records
4) So now that I know about a dictionary object, I believe I need to create one Dictionary object for EACH record (row) of my data and then store those Dictionary objects within a one dimensional array such that each element of the array is a Dictionary.
5) This way I can iterate through each element of the array to access the dictionary inside and perform calculations on each record, depending on which key/item I needed to work with at a later point
Questions for Dictionary experts:
1) Is my approach sound?
2) What is the syntax for putting the dictionary of one row's worth of data into an element of a one-dimensional array?
View 8 Replies
View Related
Apr 20, 2012
I would like to get data onto my worksheet that looks something like what I have below. I'm pretty set on using Dictionaries to get the data into this format
(row 1 reads): header 1, header 2, header 3
(row 2 reads): value 1, value 2, value 3
(row 3 reads): value 1, value 2, {"property1": property1, "property2": property2, "property3": property3}
I'm close to doing so but having issues with my syntax.
I can refer to:
arrExport(1)("key3") to get the value in key3
but I cannot refer to:
arrExport(1)("key4")
Because key4 is what contains another dictionary.
Code:
Sub DictWithinDict()
Dim dict As dictionary, dictValue As dictionary
Dim arrExport() As dictionary
Dim i As Integer
ReDim arrExport(1 To 10) 'real data will be in thousands
[Code] .....
View 3 Replies
View Related
Dec 16, 2008
Can I use my listbox contents to populate a scripting dictionary?
Dim a, z As Long
Set dic = CreateObject("scripting.dictionary")
With Sheets("Changes")
a = ListBox1.List
'a = .Range("b1", .Range("b" & Rows.Count).End(xlUp)).Offset(, -1).Resize(, 10).Value
End With
For z = 2 To UBound(a, 1)
If Not dic.exists(a(z, 2)) Then
ReDim w(1 To 10, 1 To 1)
For zz = 1 To 10: w(zz, 1) = a(z, zz): Next
dic.Add a(z, 2), w
Else
w = dic(a(z, 2))
ReDim Preserve w(1 To 10, 1 To UBound(w, 2) + 1)
For zz = 1 To 10: w(zz, UBound(w, 2)) = a(z, zz): Next
dic(a(z, 2)) = w
End If
Next
ComboBox2.List = dic.keys
ComboBox2.Value = Sheets("Calendar").Range("E3").Value
This code doesn't work because the bold line falls over.... the Remmed statement below it works fine though...
View 9 Replies
View Related
Aug 4, 2009
removing duplicates from a the first column of a two column table while maintaining the data in the second column. Unfortunately my skills fall short and I have been unable to alter the code to work for a 5 column table.
For example, if I might have data that might look something similar to this: ....
View 10 Replies
View Related
Mar 23, 2014
I have a spreadsheet with 2 tabs:
AllData:
Name
Data
Read1
[Code]....
View 9 Replies
View Related
Feb 25, 2008
I have a front page with buttons linking to other spreadsheets, e.g
{ button } Stock Sheet .xls (???)
{ button } Sales Sheet .xls (???)
In the brackets I wanted to display the last updated date of the said spreadsheets, so that a user can see if it has already been updated. Is there a way of retrieving the last updated date for a spreadsheet, and dispaying it (i.e is it held against a variable or named item)
View 9 Replies
View Related
Jan 13, 2014
I have several sheets named by months like 1213, 1113, 1013, 913, 813, 713, etc (month-year) and I'm doing one sheet as an index that needs to find a value across all the sheets.
All the months sheets have a list of names in column A and status in column B. When the business with the name is done, it doesn't appear in the next sheet.
For example "Client A" could appear in 713, 813, 913 (business done) and won't appear in 1013, 1113 and 1213,
In the index sheet I want to lookup what is the status for a name from the latest sheet (last updated status).
So I have all the sheets names in a range called months and I want to make a small formula if possible.
I want to start looking in the latest month (sheet) and if that doesn't have any value it goes to the lower one and so on.
If I use =VLOOKUP($A$5,INDIRECT("'"&MAX(months)&"'!A:D"),4,0) it shows N/A since the name only appeared until 913 and its looking on 1213.
Right now this is working, but it's too big to understand.
IF(NOT(ISERROR(VLOOKUP($A$5,INDIRECT("'"&MAX(months)&"'!A:D"),4,0))),VLOOKUP($A$5,INDIRECT("'"&MAX(months)&"'!A:D"),4,0),
IF(NOT(ISERROR(VLOOKUP($A$5,INDIRECT("'"&MAX(months)-100&"'!A:D"),4,0))),VLOOKUP($A$5,INDIRECT("'"&MAX(months)-100&"'!A:D"),4,0),
[Code] ......
View 8 Replies
View Related
