輸入範圍判斷儲存格

yueh0720

單一儲存格判斷是可以,但是若我要範圍式可否??

下載 (30.15 KB)

2011-6-3 17:28

ginbow

用 select case  以下為範例程式碼

Sub g1()
Dim i As Integer
Dim c As Integer

i = Worksheets("sheet1").Cells(65536, 1).End(xlUp).Row

For x = 1 To i

c = Cells(x, 1)

Select Case c

Case Is < 11

Cells(x, 2) = "A"

Case Is < 21

Cells(x, 2) = "B"

Case Is < 31

Cells(x, 2) = "C"

Case Is < 41

Cells(x, 2) = "D"

End Select

Next x

End Sub

yueh0720

數值變更後,都沒半法判斷

Sub g1()
Dim i As Double
Dim c As Double

i = Worksheets("sheet1").Cells(65536, 2).End(xlUp).Row

For x = 2 To i

c = Cells(x, 1)

Select Case c

Case Is < 10000

Cells(x, 3) = "A"

Case Is < 12000

Cells(x, 3) = "B"

Case Is < 15000

Cells(x, 3) = "C"

Case Is < 17000

Cells(x, 3) = "D"

End Select

Next x

End Sub

yueh0720

已可以用了,感恩

hugh0620

樓主要的只是把區間的值帶出
         提供三種方式參考,希望可以解決樓主的問題
         函數=LOOKUP(E4,$B$4:$B$7,$A$4:$A$7)
         按鈕式 :自行設定一個按鈕,將程式碼貼上,若SHEET不一樣請自行修改
                        Private Sub CommandButton1_Click()
                                Do Until Sheet1.Range("E" & 4 + I) = ""
                                          Sheet1.Range("F" & 4 + I) = Application.Lookup(Sheet1.Range("E" & 4 + I), Sheet1.Range("B4:B7"), Sheet1.Range("A4:A7"))
                                         I = I + 1
                              Loop
                        End Sub
           自動帶出:輸入值後,自行判斷,若SHEET不一樣請自行修改
           Private Sub Worksheet_Change(ByVal Target As Range)
                          If Target.Count >= 2 Then Exit Sub
                         If Target.Column <> 5 Or Target.Offset(0, 0) = "" Then Exit Sub
                                Target.Offset(0, 1) = Application.Lookup(Target.Offset(0, 0), Sheet3.Range("B4:B7"), Sheet3.Range("A4:A7"))
           End Sub