dictionary

Greetings to all, When using an Excel dictionary, and when using either the .Items or the .Keys methods to assign the dictionary's items or keys to an array, will the array always be cleared of any existing data before the assignment is made? I'm working on a program which loops through data...

Hello everyone: I have some dictionaries with values, and I need to calculate the correlation between the values of dictionaries. Let's see the code: Sub Test() Dim Dic1 as New Scripting.Dictionary Dim Dic2 as New Scripting.Dictionary Dim Dic3 as New Scripting.Dictionary Dim Dictionaries as...

Hi, I was wondering if it is possible to create a scripting dictionary that is equivalent to a vlookup for a report that I am trying to create. Basically I have my Sheet1 where I have my list of "ID" then some other data, and the new amount, which is taken from from Sheet8 - a data dump I get...

there is going to be a data in A column of current sheet containing unique values. I want to add all these already existing unique values to a dictionary and use each of the unique value as a criteria for filtering in next sheet. How to add all the values of A column to the dictionary? and how...

Hello everyone: I have two differents subs, each one calls to a different class to build the same object. The unique difference between both is the type of item used to load the dictionary inside the class. Let's have a look on the first one: In the first one the dictionary is located inside a...

Hi. I am having some issues with VBA. I use an Excel formula found on this forum to find the Nth to last word in a cell. The formula is: TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(CELL REFERENCE);" ";REPT(" ";60));60*n);60)) My VBA script finds specific strings from Sheet1 and copies these to their...

I read that purely for speed purposes, you should use a For Each loop when looping through a collection. For arrays, use a For Next loop. What about for a dictionary? Which is faster? Thanks

Hi, Im using the VBA dictionary to make a summary report by salesrep for quantity and value. The problem is if I put an IF condition testing for the year to be 2017, one of the values comes wrong. If I remove the IF condition the code works fine and gives all the correct results. Is there a...

Hello all, i have table like this: <tbody> <tbody> texttexttextKEYVALUEtexttexttext </tbody> here i need fill value of item for if the key is found in text in column A <tbody> texttexttextKEYVALUEtexttexttext </tbody> here i need fill value of item for if the key is found in text in...

Hi, I'm using the dictionary in VBA to summarize based on the units held in one column, but I would also like the code to summarize by amount, which is in another column, can I do this in the same sub and output both the results by name for units and amount. I have pasted the example how the...

Good morning, As the title implies, I am learning how to use the dictionary object. And in doing so, my end goal is to count the number of unique entries in a 2d array with multiple criteria so that I can redim an output array to the appropriate size. With that being said, please do not just...

Hi all, I have a client comments in sheet Client Comments that I would like to paste to sheet All Comments. Client ID's are in Column AV and comments in Column CE in both sheets. The code below takes 13 minutes and doesn't paste any comments so would appreciate any support. In Phase 1, I'm...

Good day this use to Translate Languages CreateObject("MSXML2.XMLHTTP") how to be used for Speak language Tanks

Hello, I am attempting to generate an organized list of all entries from multiple sheets which have a date, vendor, cost, category, subcategory, and notes. I am looking to have them sorted by date based upon their vendor, and to display the original date, notes, and cost of the data which...

Hi, My question might sound a bit illogical and even I never thought about something like this before I encountered a problem myself. Let me try to explain what I am looking to do. Here is the code I have, it is only one function and there are a few others in the module but I think this should...

Hi Friends, I am facing a error "Subscript out of range". I am trying to add array names to dictionary. column_arr_1 = Array("X1", "Y2") column_arr_2 = Array("Z3", "A1") column_arr_3 = Array("B1", "C1") column_arr = Array(column_arr_1, column_arr_2, column_arr_3) For i...

The following VBA can change word that is not in dictionary to RED: Sub zz() <code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Dim ws As Worksheet, rng As Range, s$, w$ Set ws = ActiveSheet For Each rng In ws.UsedRange If...

Hi Team, I am learning VBA Dictionary and trying to understand the below line. I stuck only with with word Nothing. Can you someone tell what that Nothing word means. With Dict For Each Cl In Sht2.Range("A2:A" & sht2UsdRws) If Not .exists(Cl.Value) Then .Add Cl.Value...

Hi All, I have the below which is supposed to be adding a range of text values to a dictionary however when I debug.print it nothing is being added. list1 is defined as a variant. Set d = CreateObject("scripting.dictionary") list1 = ThisWorkbook.Sheets("Summary").Range("A7:A22").Value For...

Hello, I'm trying to add the key classificationLevels with a value of ["Sectors"] (yes, I need the quotes) to a dictionary When I try Body.Add "classificationLevels", "[""Sectors""]" I actually add the following with the extra ""s. ["Sectors"] I also tried Body.Add "classifiactionLevels"...