|
|
楼主 |
发表于 2020-1-17 15:45
|
显示全部楼层
通过反复测试和查证，最终确认报错的原因是因为相关区域日期格式不统一造成的，把所有日期格式重新设定后解决了问题。
前后经历了半个月时间，查阅了很多网络资料和书籍，看似白费了很多功夫，但在此过程中也学到了校验代码的基本方法，同时优化了代码。
谢谢论坛和各位老师的指导
分享最终的代码如下
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim RNG As Object
Const a As Integer = 200 'set the max of PN quantity
Const b As Integer = a * 9 'set the max of ATP area
Dim c As Date
Dim d As String
Set RNG = Target
If RNG.Column > 17 Then 'decide if the target cells are with date
For i = 0 To a Step 1
If RNG.Row = 5 + 9 * i Then 'decide if the target cells are in plan rows
d = Sheets("ATP").Cells(RNG.Row, "d").Value 'the target PN
c = Sheets("ATP").Cells(4, RNG.Column).Value 'the target date
Sheets("Manual P").Select
x = Sheets("Manual P").Range("b:b").Find(what:=d, LookIn:=xlValues).Row 'the row NO.of aim
Sheets("Manual P").Select
y = Sheets("Manual P").Range("4:4").Find(what:=c, LookIn:=xlValues, lookat:=xlWhole).Column 'the column NO.of aim
ActiveSheet.Cells(x, y).Select
Exit For
End If
Next
End If
If RNG.Column = 8 Then 'decide if the target is component
If Sheets("ATP").FilterMode Then 'decide if ATP sheet is under sorting
Sheets("ATP").ShowAllData 'clear the sorting
End If
z = Sheets("ATP").Range("d1:d" & b).Find(what:=RNG.Value, LookIn:=xlValues).Row + 7 'the row NO.of aim
Sheets("ATP").Cells(z, "d").Select
Set RNG = Nothing 'clear the target cell
End If
End Sub
|
|
楼主: lovershome
/1 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2020-1-16 10:34
