[已解决]如何用VBA自动计算填写到红色括号中(含括号) ?

lewis16888

假设 A1 输入这样 3/4" 20R*30P*1500L*10F

最后A1输入完毕按回车得出这样结果 3/4" 20R(440mm)*30P(750mm)*1500L(59.06")*10F(2.54mm)


3/4" 20R(左边20*22)*30P(左边30*25)*1500(左边1500/25.4)*10F(左边10/25.4)

请问版主跟高手要怎么写VBA

最佳答案

月排行榜 / 总排行榜

suye1010

2013-1-16 19:22

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. Dim arr, i As Integer
   
3. If Len(Target) - Len(Replace(Target, "*", "")) = 3 Then
   
4.     arr = Split(Target, "*")
   
5.     arr(0) = arr(0) & "(" & Val(Split(arr(0), " ")(1)) * 22 & "mm)"
   
6.     arr(1) = arr(1) & "(" & Val(arr(1)) * 25 & "mm)"
   
7.     arr(2) = arr(2) & "(" & Format(Val(arr(2)) / 25.4, "00.00") & """)"
   
8.     arr(3) = arr(3) & "(" & 25.4 / Val(arr(3)) & "mm)"
   
9.     Application.EnableEvents = False
   
10.         Target = Join(arr, "*")
    
11.     Application.EnableEvents = True
    
12. End If
    
13. End Sub

复制代码

跳转到最佳答案楼层

suye1010

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. Dim arr, i As Integer
   
3. If Len(Target) - Len(Replace(Target, "*", "")) = 3 Then
   
4.     arr = Split(Target, "*")
   
5.     arr(0) = arr(0) & "(" & Val(Split(arr(0), " ")(1)) * 22 & "mm)"
   
6.     arr(1) = arr(1) & "(" & Val(arr(1)) * 25 & "mm)"
   
7.     arr(2) = arr(2) & "(" & Format(Val(arr(2)) / 25.4, "00.00") & """)"
   
8.     arr(3) = arr(3) & "(" & 25.4 / Val(arr(3)) & "mm)"
   
9.     Application.EnableEvents = False
   
10.         Target = Join(arr, "*")
    
11.     Application.EnableEvents = True
    
12. End If
    
13. End Sub

复制代码

lewis16888

太牛了,谢谢版主哥哥