1. C

    How to use dictionary method instead countif?

    I want to use vba dictionary method to calculate over 100k rows instead of in worksheet countif function to reduce time of calculation but I could not find how to convert my formula which shown in below. Range("AG2").Formula = "=COUNTIF($AF$2:$AF100000,"YES" & X2 & Z2 & "TRUE")"...
  2. J

    Compiling and accessing nested collection within a nested dictionary

    Hi, First time posting a thread, long time user/reader of threads. I need help solving a problem that I can't find the answer (or close to the answer) to. What I want to do: Given a table of data which includes Part number, quote date, quantity, price and supplier, I want iterate through the...
  3. D

    Dictionary, Collection or Array?

    i have the following excel sheet and need to reformat the data from a vertical view to a horizontal one while lining up the data in the appropriate columns. the vertical column is labeled Key and Value ("A:B") the reformatted output is labeled to the right called Key, followed up by the keys...
  4. DataBlake

    Using inputbox to check dictionary

    Sub findQTY() Dim pnum As Variant pnum = InputBox("Please Type in the part number") If dicQTY.exists(pnum) Then MsgBox dicQTY(pnum) Else MsgBox "No quantity available for this part number" End If End Sub the objective is to have the user enter a part number and it will either return the...
  5. DataBlake

    Personal Workbook Macro Punlic Dictionary Set Up [vba]

    I want to create some dictionaries that i can access in any workbook. I.E a list of colors as the key and a list of shortened versions of that color. Gloss Black White Trim = key Black = Value i have a sheet with these values that i would like to establish as a disctionary A: will this work if...
  6. mole999

    Scripting Dictionary

    I know these appear really fast, I have a worksheet that grows every day and currently 98% has to be looked up manually (basically a location, that then develops into postcode ad easting & northing plus A&B identifier's In the table shown as A I am developing values in Col AN I want to keep...
  7. M

    Possible to check if user is on a MAC using ActiveX or Scripting.Dictionaries ?

    I would like to know if creating these two objects while on a MAC would create an error that can be used to adjust the workbook accordingly. Thank you for your time.
  8. M

    Placing an Autofiltered range inside an array works only once

    I'm currently having a problem where an auto-filtered range is only placed inside an array on the first loop but subsequent attempts to do the same despite the range filtering correctly don't work. Set OpenWBS = Workbooks(Workbooks.Count).Worksheets(1).UsedRange...
  9. J

    Data Dictionary Best Practices

    This is probably something simple but I just wanted to know all my available options. I own and maintain a data dictionary that I keep on Excel. There are around 12 columns, all with headers. The columns are meant to identify all the attributes of a particular data element. Each row is a...
  10. N

    Late Binding a Public Dictionary object does NOT work

    I found below code to VLookup with cell format, it works only when you add "Microsoft Scripting Runtime" library to the workbook's VBA Project (via. Tools > Add > References) However, to remove the need to add above library, I modified a part of the first code as below, but It Does Not Work...
  11. E

    Parsing Key Value pairs from inconsistent and incomplete data

    Hey all, I have a CSV file with the following data in rows: <tbody> {'gender': 'male', 'age': '35', 'hometown': 'Chicago', 'car': 'Civic', 'occupation': 'actor', 'salary': '80000'} {'gender': 'male', 'age': '32', 'hometown': 'New York', 'occupation': 'banker', 'salary': '130000'}...
  12. E

    dictionary question

    Hi, is it possible two have two keys for each value? Here's the problem I'm facing with. I have some balance sheets for different people with different dates in different worksheets and files. I want to extract certain data such as the numbers of equity or debt from balance sheet, and rearrange...
  13. A

    Nested Dictionary?

    I have a set of data: Domains; Users; Machines I have a task that requires me to check to see if a domain user has logged into a particular machine in the past. There are unlimited domains, an undefined number of users, and the machine name could potentially change at any time. The tool has to...
  14. azizrasul

    Keeping a dictionary in scope

    I have Dim MyDict As Dictionary at the top of a module so that it can be used by multiple functions. How do I enable the values in MyDict to remain in memory when I use MyDict.Add appAccess.currentproject.Name, appAccess so that I can use the values in MyDict in separate functions within...
  15. R

    Printing Dictionary Values

    Hi I can print Dictionary values to the immediate window by running the code below, but I wanted to know how I print "all" the Dictionary values into an Excel sheet? Public Sub DictionaryExamples() Dim exampleValues As Variant Dim i As Long Dim aKey As String Dim aValue As...
  16. O

    Using VBA to convert JSON, with multi-level dictionaries

    Hi All, I am trying to use the NVD database JSON files (available to download from https://nvd.nist.gov/vuln/data-feeds - ) so I can construct a sheet of all the vulnerailities and then I can sort them in Excel. Yes, I realise that NVD does offer a search function so I could limit it to Oracle...
  17. G

    scripting dictionary with 2 Keys and 1 Item

    hello all, Normally I would use Index/Match but I'm looking for a VBA solution. In column A I have a list of numbers. In column B I have a list of names. In column C I have a date. Can a scripting dictionary have 2 keys (in column A & B) and 1 item (in column C)? Match the value in columns...
  18. S

    Delete all words not found in dictionary

    I have a table with several thousand foreign words (Hrvatski to be precise which is spoken in Croatia) which is mixed with a few English words. What I need to do is delete all the words in this table (words are in columns A-D) that are NOT found in the English Excel dictionary. In other words...
  19. J

    Scripting Dictionary help when there is a comma in the string

    Hello, I am looking for some assistance in using a Scripting Dictionary to read a column of data and store unique values for output into a list. The problem i am coming across is the Scripting Dictionary is parsing lines in separately when it sees a comma. For example: AL - Medical Group...
  20. 3

    Check If Anything Added to Dictionary

    Hi, I declared a variable as dictionary. Wonder if there is a way to check if anything been added to the dictionary. I have the following code but it says that the dictionary is NOT EMPTY. Sub store_Dict() Dim Dict_curUpdate As New Dictionary If Not Dict_curUpdate Is Nothing Then...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back