Dim SpecDict AsObject' Claim a dict to store spec rows in worksheet SPEC.
Dim DataDict AsObject' Claim a dict to store rawdata rows in worksheet Data.
Set SpecDict = CreateObject("Scripting.Dictionary")
Set DataDict = CreateObject("Scripting.Dictionary")
Call setDict("SPEC", 3, SpecDict, Worksheets("SPEC").UsedRange, True)
Call setDict("Data", 2, DataDict, Range(Worksheets("Data").Names(1)), True)
PublicFunctionsetDict(ByVal sheetName AsString, ByVal Target AsInteger, ByRef Dict AsObject, ByVal mRange As Range, OptionalByVal byRows AsBoolean=True)
Dim nowSheet As Worksheet
IfNot IsExistSheet(sheetName) ThenExitFunctionSet nowSheet = Worksheets(sheetName)
Dim i AsLongDim n AsLongOnErrorResumeNextIf byRows =TrueThenFor i = 1 To mRange.Rows.Count
IfNot Trim(mRange.Cells(i, Target).Value) =""Then Dict.Add mRange.Cells(i, Target).Value, i
EndIfNext i
ElseFor i = 1 To mRange.Columns.Count
IfNot Trim(mRange.Cells(Target, i).Value) =""Then Dict.Add mRange.Cells(Target, i).Value, i
EndIfNext i
EndIfEndFunction
PublicFunctiongetSPECByPara(ByVal nowPara AsString, ByVal n As specColumn, Optional sheetName AsString="SPECTEMP")
Dim reValue
Dim nowRange As Range
Dim TargetSheet As Worksheet
If Left(nowPara, 1) ="'"Then nowPara = Mid(nowPara, 2)
Set TargetSheet = Worksheets(sheetName)
Set nowRange = TargetSheet.UsedRange
OnErrorResumeNext reValue = TargetSheet.Cells(SpecDict(nowPara), n)
IfNot IsEmpty(reValue) ThenIf Trim(reValue) =""ThenSet reValue =NothingEndIf getSPECByPara = reValue
EndFunction