|
楼主 |
发表于 2018-1-4 11:52
|
显示全部楼层
版主我在网上恶补了一下like正则表达式
Private Sub Worksheet_Change(ByVal Target As Range)
Dim str1, arr, crr, x%, r%, reg
If Target.Column = 1 Then
r = Target.Row
Set reg = CreateObject("vbscript.regexp")
str1 = Target.Value
'If str1 Like "*+*" = False And str1 Like "*-*" = False And str1 Like "***" = False And str1 Like "*/*" And str1 Like "*.*" = False Then
If str1 Like "*+*" = False Then Exit Sub
'If str1 Like "*-*" = False Then Exit Sub
'If str1 Like "(-?\d+)(\ *[+\-*/]\ *-?\d+)*" = False Then Exit Sub
arr = Split(str1, "+")
str1 = ""
For x = 0 To UBound(arr)
With reg
.Global = True
.Pattern = "\d+|[一-龡][a-zA-Z]+"
'.Pattern = "\d+[a-zA-Z]"
'.Pattern = "\d+|[a-zA-Z]"
crr = Split(.Replace(arr(x), "$&,"), ",")
If UBound(crr) > 0 Then
If crr(1) = "" Then
str1 = str1 & crr(0) & "+"
Else
str1 = str1 & crr(0) & "(" & crr(1) & ")" & "+"
End If
Else
str1 = str1 & "(" & crr(0) & ")" & "+"
End If
End With
Next x
Cells(r, 2) = Left(str1, Len(str1) - 1)
End If
End Sub
我尝试了很多,现在您帮我写的代码中除了加法,其他都不能用。其实我的想法是解决单元格数字后面如果有汉字或字母就自动加英文的括号,然后我就能用宏表函数evaluate进行计算了。以前是定义名称然后计算,但是有时候因为EXCEL版本或同事在别的电脑上的原因定义名称就不起作用了。
每次单元格汉字都需要加个[],我想智能化一点所以就有了这么个提问了
|
|