VBA编译错误类型不匹配-循环与表函数

Question

目标:循环遍历包含文本行的列，如果基于工作表函数Search()条件存在匹配，则将值输出到另一个工作表。

问题:搜索不匹配错误函数。第一行数据的Search()函数返回#VALUE。

已尝试:在包含"X="我能够输出所需的值。

Sub practice()
Dim coordinate As Variant
Dim myData As Range
Dim count As Integer
count = 2
Set myData = Worksheets("DATA").Range("A:A")
For Each coordinate In myData
'end loop at blank cell
If coordinate = "" Then
Exit For
End If

If Application.WorksheetFunction.IfError(Application.Search("X=", coordinate, 1), "") <> "" Then
Sheets("COORDINATES").Range("A" & count) = Application.WorksheetFunction.Mid(coordinate, Application.Search("X=", coordinate, 1) + 2, Application.Search("Y=", coordinate, 1) - Application.Search("X=", coordinate, 1) - 2)
Sheets("COORDINATES").Range("B" & count) = Applicatoin.WorksheetFunction.Mid(coordinate, Application.Search("Y=", coordinate, 1) + 2, Application.Search("Z=", coordinate, 1) - Application.Search("Y=", coordinate, 1) - 2)

count = count + 1
End If
Next coordinate
End Sub

编辑:添加IfError和blank可以解决if语句。

现在期望的mid(search())组合返回运行时438错误"对象不支持此属性或方法"

Answer 1

Mid()不需要WorksheetFunction。应用

Sub practice()
Dim coordinate As Variant
Dim myData As Range
Dim count As Integer
count = 2
Set myData = Worksheets("DATA").Range("A:A")
For Each coordinate In myData
'end loop at blank cell
If coordinate = "" Then
Exit For
End If

If Application.WorksheetFunction.IfError(Application.Search("X=", coordinate, 1), "") <> "" Then 'Using IfError(Search(),"") resolved the #VALUE issue.
'Syntax Mid() does not require WorksheetFunction.Application
Sheets("COORDINATES").Range("A" & count) = Mid(coordinate, Application.Search("X=", coordinate, 1) + 2, Application.Search("Y=", coordinate, 1) - Application.Search("X=", coordinate, 1) - 2)
Sheets("COORDINATES").Range("B" & count) = Mid(coordinate, Application.Search("Y=", coordinate, 1) + 2, Application.Search("Z=", coordinate, 1) - Application.Search("Y=", coordinate, 1) - 2)

count = count + 1
End If
Next coordinate
End Sub