我有代码
Set wsDest = Workbooks.Open("G:\Manager Folder\Test\Leave Tracker Master.xlsx").Worksheets("Tracker")
Set myrange1 = wsDest.Range("A:A") 'to be changed to select current range
en = wsCopy.Range("c4").Value
st = myrange1.Find(en)
st1 = wsCopy.Range("B7").Value
MsgBox st
运行良好并在消息框中返回正确的值,
但是当我编辑它以包含“If 语句”时,它会在“如果 St 为 Nothing 则”行上出现类型不匹配错误
st 设置为“Long”
有人可以帮帮我吗!
Set wsDest = Workbooks.Open("G:\Manager Folder\Test\Leave Tracker Master.xlsx").Worksheets("Tracker")
Set myrange1 = wsDest.Range("A:A") 'to be changed to select current range
en = wsCopy.Range("c4").Value
st = myrange1.Find(en)
st1 = wsCopy.Range("B7").Value
If st Is Nothing Then
Resume
Else
Do While st1 <= wsCopy.Range("B9").Value
If (st.Offset(0, 4) = st1) Then
MsgBox "Leave Request already exists"
Exit Sub
End If
st1 = st1 + 1
st = myrange1.FindNext(st)
Loop
End If
答案1
如果尚不存在,请将其放在代码窗口的顶部:
Option Explicit
尽量记住将其放在每个代码窗口的顶部。这将迫使您在使用变量之前对其进行声明。
您将需要用明确的类型声明变量st
(以及其他变量),如下所示:
Dim st As Range
然后在使用该Range.Find
方法时,您将需要使用Set
关键字:
Set st = myrange1.Find(en)