我需要比较字符串中的小字符串。例如,我有两列,分别是 A 和 B
A B
test_BL_pa.txt 1
test_AL_pa.txt 2
test_BL_pa.txt 3
test_CL_pa.txt 4
输入是 test_BL、test_AL、test_CL。我想要获取与 A 列对应的 B 列的值。因此,给出输出:
test_BL 4(1+3)
test_AL 2
test_CL 4
你能帮我用 Excel 实现它吗
答案1
这个宏可以做到
Sub UpdateStatus()
Dim row As Integer
row = 1 ' sets the starting row
Dim statisticRow As Integer
statisticRow = 1 ' sets the starting row for the results
Do While (True)
Dim currentValue As String
currentValue = Range("A" & row).Value
Dim otherValue As String
If currentValue = "" Then
Exit Do
End If
Dim otherRow As Integer
otherRow = 1 ' sets the starting row where the results are
Do While (True) ' find it or add it
otherValue = Range("F" & otherRow).Value
Dim currentValueStatus As String
If Left(currentValue, 7) = otherValue Then ' As expected sire, I found it. Can I eat now?
currentValueStatus = Range("B" & row).Value
Range("G" & otherRow).Value = Range("G" & otherRow).Value + Range("B" & row).Value
Exit Do
End If
otherRow = otherRow + 1
Loop
row = row + 1
Loop
End Sub
如下图所示,我必须在 F 列中设置条件。由于您的帖子显示“小”字符串长度为 7 个字符,并且始终是大字符串的前 7 个字符,因此我们可以使用 Left() 函数。如果情况并非总是如此,请使用instr() 函数
运行宏后