Acronyms/Abbreviations List

sanityendshere

New Member
Joined
Aug 7, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I've written a scripting dictionary (posted below) which includes interpreted qualifiers, acronyms, and abbreviations that I commonly use in tables. The script below just prints out the entire dictionary. How do I write a script that will search through multiple workbook pages for the dictionary items and if they are present list them in the workbook? I'm trying to do this to avoid having to search the tables manually to figure out which of these keys have been used and which have not. Cutting out the manual part of this process would greatly reduce the possibility of errors when making the lists. TIA

Sub Dictionary()

Dim myDict As Dictionary

Set myDict = New Dictionary

myDict.Add "J", "J = Estimated concentration"
myDict.Add "J-", "J- = Estimated concentration, biased low"
myDict.Add "J+", "J+ = Estimated concentration, biased high"
myDict.Add "U", "U = The analyte was not detected at a level greater than or equal to the adjusted detection limit (DL)"
myDict.Add "UJ", "UJ = The analyte was not detected at a level greater than or equal to the adjusted DL. However, the reported adjusted DL is approximate and may be inaccurate or imprecise."
myDict.Add "UX", "UX/X = The presence or absence of the analyte cannot be substantiated. Acceptance or rejection of the data should be decided by the project team, but exclusion of the data is recommended."
myDict.Add "X", "UX/X = The presence or absence of the analyte cannot be substantiated. Acceptance or rejection of the data should be decided by the project team, but exclusion of the data is recommended."

myDict.Add "AOI", "Area of Interest"
myDict.Add "DUP", "Duplicate"
myDict.Add "FD", "Duplicate"
myDict.Add "ft", "ft"
myDict.Add "LOD", "Limit of Detection"
myDict.Add "LOQ", "Limit of Quantitation"
myDict.Add "ND", "Analyte not detected above the LOD"
myDict.Add "Qual", "Interpreted Qualifier"
myDict.Add "ug/Kg", "micrograms per Kilogram"
myDict.Add "ng/L", "nanograms per Liter"
myDict.Add "-", "Not applicable"

Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Sheet1")

' Write keys to range A1:A43
sh.Range("A1:A43").Value = WorksheetFunction.Transpose(myDict.Keys)

' Write items to range A1:A43
sh.Range("B1:B43").Value = WorksheetFunction.Transpose(myDict.Items)

End Sub
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
L

Legacy 456155

Guest
Hi Insane. How are your abbreviations represented in your worksheet cells? As part of other text? Alone in any given cell? Which ranges? My first thought would be to search on each abbreviation using Find/Find Next and simply write them as they are found. I don't see the benefit of storing your pairs in a dictionary for this task. I surely would not hard-code them unless you are certain they will never change. You might consider placing your name/value pairs in some range. A named range. Loop through the range using code that will continue working even if your list changes in some way.

search through multiple workbook pages

Please clear this up for me. Are you speaking of multiple worksheets within a single workbook? Multiple workbooks?

"I wish to search for a list of abbreviations in [WHICH WORKSHEETS?]. The abbreviations may be represented as [PART? and/or WHOLE?] of the text in [WHICH RANGES?]. The list is contained in a named range with two columns. Column 1 contains the abbreviation; column 2 contains the definition. I then want to list FOUND abbreviations in Sheet1.Range("A1:A?")."
 

sanityendshere

New Member
Joined
Aug 7, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hey dataluver,

I've attached a picture of how it reports in my tables. It's basically a footnotes section under the table area. These tables are generated from our database and at times span over +15 sheets. I have been using find/find next method; however, if I can automate the search it would be extremely helpful. Only reason I thought about hard coding is that this list is pretty static.

Hopefully, me filling in your bracketed questions helps clarify

"I wish to search for a list of abbreviations in [All worksheet in the workbook]. The abbreviations may be represented as [PART? and/or WHOLE?] of the text in [B1:AD24]. The list is contained in a named range with two columns. Column 1 contains the abbreviation; column 2 contains the definition. I then want to list FOUND abbreviations in Sheet1.Range("A1:A18")."
 

Attachments

  • Acronyms_Abbreviations_Qualifiers.png
    Acronyms_Abbreviations_Qualifiers.png
    27 KB · Views: 3

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,494
Members
414,071
Latest member
hijackhippo

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
Top