|
Public Function CHAZHAO(str As String, range) As String
Dim xuanze
xuanze = 0
For Each cel In range
If (InStr(cel, str) > 0) And (xuanze = 0) Then
CHAZHAO = Worksheets("Sheet1").Cells(cel.Row, 1)
xuanze = 1
ElseIf (InStr(cel, str) > 0) Then
str1 = Worksheets("Sheet1").Cells(cel.Row, 1)
MsgBox str1
Exit Function
End If
xuanze = 0
Next
End Function
很简单啊!
譬如单元格首页“H4”,=chazhao($E$1,INDIRECT(T43))
$E$1 为被查找内容,
INDIRECT(T43)为查找区域,
T43为"="sheet1!$"&T34&"1:$"&T34&"200"" ,即sheet1!&R$1:$R$200,
若在此范围找到H4的内容,则显示那行行首内容,否则为空。
若出现2个则表示同时上2个班,出错了!会提示哪一班被重复排课了。
|
|