问题描述
我正在使用.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 sixEasier 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方法进行故障排除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！