效果详见图片~
æ¶é´å ³ç³»ï¼å çå¤é¨åï¼
VBA代ç ï¼
å ¬å¼ï¼
è½ç»ä¸ªæ»¡è¶³3个è¦æ±ç代ç åï¼æåå¨ççï¼ä½ å å¿å«ç
温馨提示:答案为网友推荐,仅供参考
第1个回答 2015-07-31
B列如果有问题,会在公式中有文字吗?追问
Dim R As Range, Rng As Range
On Error Resume Next
Application.EnableEvents = False
Set R = Intersect(Target, Columns(2))
If R Is Nothing Then
Else
For Each Rng In R
Rng.Offset(, 1) = JiSuan(Rng.Text)
Next
End If
Set R = Intersect(Target, Columns(3))
Set Rng = Nothing
If R Is Nothing Then
Else
Set Rng = Columns(3).Find("sum(", , , xlPart, xlByColumns, xlNext, False, , False)
If Rng Is Nothing Then
Columns(5).Replace "合计", ""
Else
Rng.Offset(, 2) = "合计"
End If
End If
Application.EnableEvents = True
End Sub
Function JiSuan(Str As String) As Double
On Error Resume Next
For I = 1 To Len(Str)
S = Mid(Str, I, 1)
If S Like "[0-9()+-/*]" Then S1 = S1 & S
Next
If Str <> "" Then
JiSuan = Evaluate(S1)
Else
JiSuan = ""
End If
End Function
B列有问题,什么意思? B列都是输入的数字,数学计算式
追答B列如果有文字,会不会在公式中间的?还是文字都是在整个公式前或者后。
另外 E 列添加 "合计" 后,你后面说的汇总是什么意思?
汇总就是合计的意思 就是我一旦对C列几个单元格SUM 求和 对应的E列自动出现合计 二字,本来应该手动去写,我怕麻烦,想让VBA自动出现,因为有很多这种情况
文字会出现在 计算式的任意位置
Dim R As Range, Rng As Range
On Error Resume Next
Application.EnableEvents = False
Set R = Intersect(Target, Columns(2))
If R Is Nothing Then
Else
For Each Rng In R
Rng.Offset(, 1) = JiSuan(Rng.Text)
Next
End If
Set R = Intersect(Target, Columns(3))
Set Rng = Nothing
If R Is Nothing Then
Else
Set Rng = Columns(3).Find("sum(", , , xlPart, xlByColumns, xlNext, False, , False)
If Rng Is Nothing Then
Columns(5).Replace "合计", ""
Else
Rng.Offset(, 2) = "合计"
End If
End If
Application.EnableEvents = True
End Sub
Function JiSuan(Str As String) As Double
On Error Resume Next
For I = 1 To Len(Str)
S = Mid(Str, I, 1)
If S Like "[0-9()+-/*]" Then S1 = S1 & S
Next
If Str <> "" Then
JiSuan = Evaluate(S1)
Else
JiSuan = ""
End If
End Function
代码放到对应工作表中
代码可以满足 自动计算,但是不满足第二条,我对C列的结果 几个单元格求和 没有合计 二字跳出
追答我测试没问题。
看你的
私
。。。
信。