如果单元格包含文本的某些文本复制部分并移动到新表中

Im trying to use VBA in order to detect cells which contain the word HELLO and then:

我试着用VBA来检测包含“HELLO”这个词的细胞:

take the 7th to 10th characters and copy those to a new sheet on the first available row

取第7到第10个字符，并将其复制到第一个可用行的新表中。

then copy the 12th to last character to a second column on the new sheet.

然后将第12到最后一个字符复制到新表的第二列。

Repeat for all cells containing the phrase.

重复所有包含短语的单元格。

Right now I can’t get the code to copy the first cell that contain the phrase.

现在我无法让代码复制包含短语的第一个单元格。

This is the current code:

这是当前的代码:

Sub test()
Dim LR As Long, i As Long
With Sheets("Sheet1")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If .Range("A" & i) Like "*HELLO*" Then
    .Copy Mid(Range("A" & i), 2, 2)

Next i
End Sub

2 个解决方案

#1

Instead of copying, it would be better just to assign the partial string value into the next cell in the second sheet. I also added UCASE to your if statement in case the HELLO isn't capitalized. Then added an If to check if the string was 12 characters long atleast before returning the 12th to last character.

与其复制，不如将部分字符串值分配到第二个表中的下一个单元格中。我还向你的if语句添加了UCASE，以防HELLO没有大写。然后添加一个If来检查字符串是否至少12个字符，至少在返回第12个字符之前。

Sub test()
Dim LR As Long, i2 As Long


LR = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
i2 = 1

For i = 1 To LR
    If UCase(Sheets(1).Range("A" & i).Value) Like "*HELLO*" Then
        Sheets(2).Range("A" & i2).Value = Mid(Sheets(1).Range("A" & i).Value, 7, 3)
        If Len(Sheets(1).Range("A" & i).Value) > 11 Then
            Sheets(2).Range("B" & i2).Value = Mid(Sheets(1).Range("A" & i).Value,13, Len(Sheets(1).Range("A" & i).Value) - 12)
        End If
        i2 = i2 + 1
    End If
Next i

End Sub

#2

You probably can't copy it, you may have yo just place it in another cell location something like

你可能不能复制它，你可以把它放在另一个单元格位置，比如

Sub Button1_Click()

    Dim LR As Long, i As Long
    With Sheets("Sheet1")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To LR
            If .Range("A" & i) Like "*HELLO*" Then
                Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Mid(Range("A" & i), 2, 2)
            End If
        Next i
    End With
End Sub

Edit: Ah, somebody else had the same idea.

编辑:啊，别人也有同样的想法。