代码之家 › 专栏 › 技术社区 › Clauric

WorksheetFunction.find没有更多要查找的值

vba excel

-1

Clauric · 技术社区 · 5 年前

我有以下代码可以在单元格中查找单个单词。为了找到单词,它使用worksheetfunction.find命令查找这些单词之间的空格。

这个过程在到达单元中的最后一个字之前都可以正常工作。由于没有更多的空间可供查找,它将返回一个错误。我已经尝试使用application.find命令来解决这个错误,但是当我这样做时,它会将所有内容都视为一个错误,并且只选择单元格中的所有文本。

我想知道的是:

使用worksheetfunction.find命令,有没有办法解决在最后一个空格和单元格结尾之间找不到空格的问题?
application.find命令返回什么错误(不太重要)?

Dim a As Double
Dim b As Double
Dim c As Variant
Dim d As Integer
Dim e As String
Dim f As Double
Dim g As Variant
Dim h As Variant
Dim i As Integer

a = 1
f = 2
i = 1
b = Len(Cells(i, 1))

While Cells(i, 1) <> vbNullString
    While a < b

        c = vbNullString
        d = 0
        e = vbNullString

        c = WorksheetFunction.Find(Chr(32), Cells(i, 1), a)

        If Not IsError(c) Then

            d = c - a

        ElseIf IsError(c) Then

            d = b - a

        End If

        e = Mid(Cells(1, 1), a, d)

        If Left(e, 4) = "true" Then
            e = "'" & e

        ElseIf Left(e, 5) = "false" Then
            e = "'" & e

        End If


        If e <> vbNullString Then
            Worksheets("Words").Cells(f, 1) = WorksheetFunction.Trim(e)
            f = f + 1

        End If

        If Not IsError(c) Then
            a = c + 1
        Else
            a = a + d
        End If

    Wend
    i = i + 1
    b = Len(Cells(i, 1))
    a = 1
Wend

1 回复 | 直到 5 年前

dwirony Greg Viers 5 年前

尝试此解决方案:包装 Find 在一个 On Error Resume Next ,那么如果您的返回结果是空字符串,它将打击您的 Else 条件,您可以在单元格中得到最后一个词:

编辑:经过进一步的测试,当移到下一行时,这似乎有点搞砸了…但这以前有用吗?

Dim a As Double
Dim b As Double
Dim c As Variant
Dim d As Integer
Dim e As String
Dim f As Double
Dim g As Variant
Dim h As Variant
Dim i As Integer

a = 1
f = 2
i = 1
b = Len(Cells(i, 1))

While Cells(i, 1) <> vbNullString
    While a < b

        c = vbNullString
        d = 0
        e = vbNullString

        On Error Resume Next
        c = WorksheetFunction.Find(Chr(32), Cells(i, 1), a)
        On Error GoTo 0

        If c <> "" Then

            d = c - a

        Else

            d = b - a + 1

        End If

        e = Mid(Cells(1, 1), a, d)

        If Left(e, 4) = "true" Then
            e = "'" & e

        ElseIf Left(e, 5) = "false" Then
            e = "'" & e

        End If


        If e <> vbNullString Then
            Worksheets("Words").Cells(f, 1) = WorksheetFunction.Trim(e)
            f = f + 1
        End If

        If c <> "" Then
            a = c + 1
        Else
            a = a + d
        End If

    Wend
    i = i + 1
    b = Len(Cells(i, 1))
    a = 1
Wend

如果我是白手起家写的话,我会这样做。通过使用 Split ,我们可以用一个特定的分隔符(在我们的示例中是空格字符)分隔单元格的值,然后将所有这些字符写入其目标:

Option Explicit
Sub Test()

Dim sht As Worksheet
Dim i As Long, j As Long, k As Long
Dim temparr As Variant

Set sht = ActiveSheet
k = 1

For i = 1 To sht.Cells(sht.Rows.Count, 1).End(xlUp).Row
    temparr = Split(sht.Cells(i, 1).Value, " ")
    For j = 0 To UBound(temparr)
        Sheets("Words").Cells(k, 1).Value = temparr(j)
        k = k + 1
    Next j
Next i

End Sub