楼主
xyh9999 |
'正向与反向搜索的函数
- Function Lqc_cx(gWhat, cSheet, cRange, nFX) '返回行号|||gWhat为搜索内容,cSheet被搜工作表,cRange被搜范围, nFX搜索方向--1为正向 2为逆向 默认为正向
- If nFX = 2 Then
- Set PP = Sheets(cSheet).Range(cRange).Find(What:=gWhat, MatchCase:=False, LookAt:=xlWhole, SearchDirection:=xlPrevious)
- Else
- Set PP = Sheets(cSheet).Range(cRange).Find(What:=gWhat, MatchCase:=False, LookAt:=xlWhole)
- End If
- If Not PP Is Nothing Then
- Lqc_cx = PP.Row
- End If
- End Function
- '→→→→→→→→→→→→→-
- Function Lqc_nDW_lie(LqcValue, c_lie_Range, cSheet) '精确定位数值型数据LqcValue在cSheet中的第几行,返回行号,没找到时返回-1
- lsDW = Evaluate("MATCH(" & LqcValue & "," & cSheet & "!" & c_lie_Range & ",0)")
- If IsError(lsDW) Then
- Lqc_nDW_lie = -1
- Else
- lsHang = Range(c_lie_Range).Row
- Lqc_nDW_lie = lsHang + lsDW - 1
- End If
- End Function
- '*****
- Function Lqc_cDW_lie(LqcValue, c_lie_Range, cSheet) '精确定位字符型数据LqcValue在cSheet中的第几行,返回行号,没找到时返回-1
- lsDW = Evaluate("MATCH(" & """" & LqcValue & """" & "," & cSheet & "!" & c_lie_Range & ",0)")
- If IsError(lsDW) Then
- Lqc_cDW_lie = -1
- Else
- lsHang = Range(c_lie_Range).Row
- Lqc_cDW_lie = lsHang + lsDW - 1
- End If
- End Function
- '*****************************
- Function LqcNumber2Hang(nNumber, c_lie_Range, cSheet) '由数值得到行号(数值型或字符型均可)
- '如:? LqcNumber2Hang(" 1.3 ", "I6:I600", "TEST")
- '如:? LqcNumber2Hang(1.3, "I6:I600", "TEST")
- XnNumber = Round(val(nNumber), 6)
- Set dmgm_P = Sheets(cSheet).Range(c_lie_Range).Find(What:=XnNumber, MatchCase:=False, LookAt:=xlWhole)
- If dmgm_P Is Nothing Then
- LqcNumber2Hang = ""
- Else
- LqcNumber2Hang = dmgm_P.row()
- End If
- End Function
|