- 帖子
- 559
- 主題
- 58
- 精華
- 0
- 積分
- 626
- 點名
- 0
- 作業系統
- win8
- 軟體版本
- office2013
- 閱讀權限
- 50
- 性別
- 男
- 來自
- TW
- 註冊時間
- 2010-11-22
- 最後登錄
- 2024-6-14
|
本帖最後由 hugh0620 於 2011-4-8 18:26 編輯
回復 1# a703130
樓主要的是不是如下圖的結果呢???
自行帶出是要按鈕式??或是輸入完就自行帶出??
需求不一樣~ 寫法就不一樣唷??
先寫一個輸入完就自行帶出
依您設定的cells(4,10)的位置撰寫
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Column
Case 10
If Target = "" Or Target.Offset(0, 1) = "" Or Target.Offset(0, 2) = "" Then Exit Sub
If Target = "U2" Then 'U2=0 ~ 65535
If Target.Offset(0, 1) >= 0 And Target.Offset(0, 1) <= 65536 Then '判斷最小值
Min1 = Target.Offset(0, 1)
Else
Min1 = 0
End If
If Target.Offset(0, 2) >= 0 And Target.Offset(0, 2) <= 65536 Then '判斷最大值
Max1 = Target.Offset(0, 2)
Else
Max1 = 65535
End If
Target.Offset(0, 5) = "0 ~ 65535"
ElseIf Target = "U1" Then 'U1=0 ~ 255
If Target.Offset(0, 1) >= 0 And Target.Offset(0, 1) <= 255 Then '判斷最小值
Min1 = Target.Offset(0, 1)
Else
Min1 = 0
End If
If Target.Offset(0, 2) >= 0 And Target.Offset(0, 2) <= 255 Then '判斷最大值
Max1 = Target.Offset(0, 2)
Else
Max1 = 255
End If
Target.Offset(0, 5) = "0 ~ 255"
ElseIf Target = "S1" Then 'S1=-127 ~ +127
If Target.Offset(0, 1) >= -127 And Target.Offset(0, 1) <= 127 Then '判斷最小值
Min1 = Target.Offset(0, 1)
Else
Min1 = -127
End If
If Target.Offset(0, 2) >= -127 And Target.Offset(0, 2) <= 127 Then '判斷最大值
Max1 = Target.Offset(0, 2)
Else
Max1 = 127
End If
Target.Offset(0, 5) = "-127 ~ +127"
Else 'S2=-32767 ~ +32767
If Target.Offset(0, 1) >= -32767 And Target.Offset(0, 1) <= 32767 Then '判斷最小值
Min1 = Target.Offset(0, 1)
Else
Min1 = -32767
End If
If Target.Offset(0, 2) >= -32767 And Target.Offset(0, 2) <= 32767 Then '判斷最大值
Max1 = Target.Offset(0, 2)
Else
Max1 = 32767
End If
Target.Offset(0, 5) = "'-32767 ~ +32767"
End If
Target.Offset(0, 3) = Min1 & "~" & Max1
Case 11
If Target = "" Or Target.Offset(0, -1) = "" Or Target.Offset(0, 1) = "" Then Exit Sub
If Target.Offset(0, -1) = "U2" Then 'U2=0 ~ 65535
If Target >= 0 And Target <= 65535 Then '判斷最小值
Min1 = Target
Else
Min1 = 0
End If
If Target.Offset(0, 1) >= 0 And Target.Offset(0, 1) <= 65535 Then '判斷最大值
Max1 = Target.Offset(0, 1)
Else
Max1 = 65535
End If
Target.Offset(0, 4) = "0 ~ 65535"
ElseIf Target.Offset(0, -1) = "U1" Then 'U1=0 ~ 255
If Target >= 0 And Target <= 255 Then '判斷最小值
Min1 = Target
Else
Min1 = 0
End If
If Target.Offset(0, 1) >= 0 And Target.Offset(0, 1) <= 255 Then '判斷最大值
Max1 = Target.Offset(0, 1)
Else
Max1 = 255
End If
Target.Offset(0, 4) = "0 ~ 255"
ElseIf Target.Offset(0, -1) = "S1" Then 'S1=-127 ~ +127
If Target >= -127 And Target <= 127 Then '判斷最小值
Min1 = Target
Else
Min1 = -127
End If
If Target.Offset(0, 1) >= -127 And Target.Offset(0, 1) <= 127 Then '判斷最大值
Max1 = Target.Offset(0, 1)
Else
Max1 = 127
End If
Target.Offset(0, 4) = "-127 ~ +127"
Else 'S2=-32767 ~ +32767
If Target >= -32767 And Target <= 32767 Then '判斷最小值
Min1 = Target
Else
Min1 = -32767
End If
If Target.Offset(0, 1) >= -32767 And Target.Offset(0, 1) <= 32767 Then '判斷最大值
Max1 = Target.Offset(0, 1)
Else
Max1 = 32767
End If
Target.Offset(0, 4) = "-32767 ~ +32767"
End If
Target.Offset(0, 2) = Min1 & "~" & Max1
Case 12
If Target = "" Or Target.Offset(0, -1) = "" Or Target.Offset(0, -2) = "" Then Exit Sub
If Target.Offset(0, -2) = "U2" Then 'U2=0 ~ 65535
If Target.Offset(0, -1) >= 0 And Target.Offset(0, -1) <= 65535 Then '判斷最小值
Min1 = Target.Offset(0, -1)
Else
Min1 = 0
End If
If Target >= 0 And Target <= 65535 Then '判斷最大值
Max1 = Target
Else
Max1 = 65535
End If
Target.Offset(0, 3) = "0 ~ 65535"
ElseIf Target.Offset(0, -2) = "U1" Then 'U1=0 ~ 255
If Target.Offset(0, -1) >= 0 And Target.Offset(0, -1) <= 255 Then '判斷最小值
Min1 = Target.Offset(0, -1)
Else
Min1 = 0
End If
If Target >= 0 And Target <= 255 Then '判斷最大值
Max1 = Target
Else
Max1 = 255
End If
Target.Offset(0, 3) = "0 ~ 255"
ElseIf Target.Offset(0, -2) = "S1" Then 'S1=-127 ~ +127
If Target.Offset(0, -1) >= -127 And Target.Offset(0, -1) <= 127 Then '判斷最小值
Min1 = Target.Offset(0, -1)
Else
Min1 = -127
End If
If Target >= -127 And Target <= 127 Then '判斷最大值
Max1 = Target
Else
Max1 = 127
End If
Target.Offset(0, 3) = "-127 ~ +127"
Else 'S2=-32767 ~ +32767
If Target.Offset(0, -1) >= -32767 And Target.Offset(0, -1) <= 32767 Then '判斷最小值
Min1 = Target.Offset(0, -1)
Else
Min1 = -32767
End If
If Target >= -32767 And Target <= 32767 Then '判斷最大值
Max1 = Target
Else
Max1 = 32767
End If
Target.Offset(0, 3) = "-32767 ~ +32767"
End If
Target.Offset(0, 1) = Min1 & "~" & Max1
End Select
End Sub |
|
發表於 2011-4-8 17:08
| 