Acronyms/Abbreviations List

[sanityendshere]

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

 

[Legacy 456155]

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]

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")."