你能帮我解决下面的vba错误吗?公式可行,但有误差。我的场景是需要在数字之间抽取(在两个"-"之间)
Ite1-223466678-ghtrdhjuyr321
Ite-654354477-hjuyt-
Dftehh-767678765-4yutiuy
只抽取"-"之间的9位数字并且需要通过VBA应用到excel的最后一行
公式:
= Value(mid(A1,search("-",A1)+1, search ("-",A1, search ("-", A1)+1-search("-", A1)-1))
VBA:
Sub Data2
Dim lastrow as long
Lastrow=Range("A"&rows.count).end(xlup).row
Range("F2:F" & lastrow).formula = " =
Value(mid(A1,search("-",A1)+1, search
("-",A1, search ("-", A1)+1-
search("-. ", A1)-1)"
End sub
误差
#VALUE错误需要修复我不知道如何做,如果搜索失败的公式
RUNTIME ERROR 13 data mismatch
试一试:
Sub BetweenDashs()
Dim lastrow As Long, i As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
arr = Split(Range("A" & i).Value, "-")
If UBound(arr) > 0 Then
Range("F" & i).Value = CLng(arr(1))
End If
Next i
End Sub
编辑# 1:
如果你的数据在列C而不是columnA,然后使用:
Sub BetweenDashs()
Dim lastrow As Long, i As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
arr = Split(Range("C" & i).Value, "-")
If UBound(arr) > 0 Then
Range("F" & i).Value = CLng(arr(1))
End If
Next i
End Sub