VBA-使用.Find方法进行故障排除 [英] VBA - troubleshooting with .Find method

问题描述

我正在使用.Find方法在一行中查找包含部分值"TL"和"CT"的单元格.该代码当前执行的操作是在C列的每一行中查找，修剪"TL-"(即"TL-"，"TL-"，"TL-")的任何变体，然后将其后的数字限制为只有6数字.例如:如果有5个数字，则将在"TL-"后面加一个0；如果有4个数字，则将在"TL-"后面加2个0.

I am using a .Find method to find cells in a row that contain part of the value "TL" and "CT". What the code currently does is look in every row in column C, trims any variation of "TL-" (ie "TL- ", "TL - ", "TL -") and then limits the numbers after it to only have 6 numbers. Example: if it has 5 numbers, it will add a 0 after the "TL-", if it has 4 numbers, it will add 2 0s after the "TL-" etc.

我在单元格中还有其他值，所以现在做什么是更改所有值以执行上述方法(如下所示)

I have other values in the cells so what it does now is change all the values to do the above method (as shown below)

Start:         Output:
TL-000872  ->  TL-000872
TL-0786    ->  TL-000786
CT-74      ->  TL-000074
GS8; 278K  ->  TL-008278

我需要做的是有效的代码，但仅在包含一些"TL"值的单元格上，执行相同的代码，但在包含某些"CT"的单元格上只有4个数字"值，然后跳过(保留原样)其他任何内容.

What I need it to do is the code that is working but only on cells that contain some "TL" value, do the same code but with only 4 numbers on cells that contain some "CT" value, and skip over (leave as is) anything else.

Start:         Output:
TL-000872  ->  TL-000872
TL-0786    ->  TL-000786
CT-74      ->  CT-0074
GS8; 278K  ->  GS8; 278K

我的.Find方法肯定无法正常工作.我认为这是主要问题；无法正确找到带有"TL"和"CT"的单元格.有什么建议吗?

My .Find method is definitely not working. I believe that is the main problem; It is not correctly finding the cells with "TL" and "CT". Any suggestions?

注意:StartSht是工作簿，其中包含所有要更改的值的代码.

NOTE: StartSht is the workbook with the code where all of the values being changed exist.

Dim str As String, ret As String, tmp As String, j As Integer, k As Integer

If Not StartSht.Range("C2").End(xlDown).Find(What:="TL", LookAt:=xlPart, LookIn:=xlValues) Is Nothing Then
For k = 2 To StartSht.Range("C2").End(xlDown).Row
    ret = ""
    str = StartSht.Range("C" & k).Value
        For j = 1 To Len(str)
            tmp = Mid(str, j, 1)
            If IsNumeric(tmp) Then ret = ret + tmp
        Next j

        For j = Len(ret) + 1 To 6
            ret = "0" & ret
        Next
        ret = "TL-" & ret
        StartSht.Range("C" & k).Value = ret

        Next k

ElseIf Not StartSht.Range("C2").End(xlDown).Find(What:="CT", LookAt:=xlPart, LookIn:=xlValues) Is Nothing Then
For k = 2 To StartSht.Range("C2").End(xlDown).Row
    ret = ""
    str = StartSht.Range("C" & k).Value
        For j = 1 To Len(str)
            tmp = Mid(str, j, 1)
            If IsNumeric(tmp) Then ret = ret + tmp
        Next j

        For j = Len(ret) + 1 To 4
            ret = "0" & ret
        Next
        ret = "CT-" & ret
        StartSht.Range("C" & k).Value = ret

Next k

Else

End If

---

该代码当前采用的TL的"TL-"后面少于6个数字，并在"TL-"之后立即加上0，直到长度为6. (即TL-0098-> TL-000098，添加了两个0).我还需要它来捕获TL是否包含6个以上的数字，并立即删除"TL-"之后的零，直到长度为6 更简单的代码可能是简单地删除-"之后的任何数字，直到长度为六.

The code currently takes a TL that has LESS than 6 numbers following the "TL-" and adds a 0 immediately following "TL-" until the length is six. (ie TL-0098 -> TL-000098, two 0s are added). I need it to also catch if TL has MORE than 6 numbers and delete the zeros immediately following "TL-" until the length is six Easier code might be to simply delete any number following the "-" until the length is six.

示例:

TL-0009999   ->  delete one 0  -> TL-009999
TL-0948398   ->  delete one 0  -> TL-948398
TL-00000008  ->  delete two 0s -> TL-000008

推荐答案

将上面的代码替换为下面的代码.

Replace your above code with the below one.

更新代码:

Dim str As String, ret As String, tmp As String, j As Integer, k As Integer

For k = 2 To Sheets("Test").Range("C2").End(xlDown).Row
        ret = ""
        str = Sheets("Test").Range("C" & k).Value

        If InStr(str, "TL") > 0 Then
            For j = 1 To Len(str)
                tmp = Mid(str, j, 1)
                If IsNumeric(tmp) Then
                    ret = ret + tmp
                ElseIf j > 5 And tmp = "T" Then
                    Exit For
                End If

            Next j

            For j = Len(ret) + 1 To 6
                ret = "0" & ret
            Next j

            If Len(ret) > 6 Then
                Debug.Print Len(ret)
                For j = Len(ret) To 7 Step -1
                If Mid(ret, 1, 1) = "0" Then
                    ret = Right(ret, j - 1)
                End If
                Next j
            End If

            ret = "TL-" & ret
            Sheets("Test").Range("C" & k).Value = ret
        ElseIf InStr(str, "CT") Then
            For j = 1 To Len(str)
                tmp = Mid(str, j, 1)
                If IsNumeric(tmp) Then ret = ret + tmp
            Next j

            For j = Len(ret) + 1 To 4
                ret = "0" & ret
            Next
            ret = "CT-" & ret
            Sheets("Test").Range("C" & k).Value = ret
        End If

      Next k

这篇关于VBA-使用.Find方法进行故障排除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！