sanityendshere
New Member
- Joined
- Aug 7, 2020
- Messages
- 7
- Office Version
- 365
- Platform
- 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
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