找出row和col問題

basarasy

請問大大如何 找出 A1:C5,E6:G10,A11:C15 只有1個儲存格的值是1的就找出它的row和col.
i1=row
j1=col

Hsieh

回復 1# basarasy

1. Sub nn()
   
2. For Each a In Range("A1:C5,E6:G10,A11:C15 ")
   
3.   If a = 1 Then i1 = a.Row: j1 = a.Column: Exit For
   
4. Next
   
5. MsgBox i1 & "    " & j1
   
6. End Sub

複製代碼

basarasy

回復 2# Hsieh

謝謝大大.
但我是想要這個.
請問大大這個可以化簡嗎?

1. Sub nn()
   
2. b = 0
   
3. For Each a In Range("A1:C5,E6:G10,A11:C15 ")
   
4. b = b + WorksheetFunction.CountIf(a, 1)
   
5. Next
   
6. 
   
7. If b = 1 Then
   
8. For Each a In Range("A1:C5,E6:G10,A11:C15 ")
   
9.   If a = 1 Then i1 = a.Row: j1 = a.Column: Exit For
   
10. Next
    
11. End If
    
12. 
    
13. MsgBox i1 & "    " & j1
    
14. 
    
15. End Sub

複製代碼

Hsieh

1. Sub nn()
   
2. If WorksheetFunction.CountIf( Range("A1:C5,E6:G10,A11:C15 "), 1) = 1 Then
   
3. For Each a In Range("A1:C5,E6:G10,A11:C15 ")
   
4.   If a = 1 Then i1 = a.Row: j1 = a.Column: Exit For
   
5. Next
   
6. End If
   
7. 
   
8. MsgBox i1 & "    " & j1
   
9. 
   
10. End Sub

複製代碼

GBKEE

Sub Ex()
    Dim Rng As Range
    Set Rng = Range("A1:C5,E6:G10,A11:C15").Find(1)
    If Not Rng Is Nothing Then MsgBox "Row " & Rng.Row & " , Column " & Rng.Column
End Sub

Hsieh

回復 5# GBKEE


    加入參數會好一點
    Set Rng = Range("A1:C5,E6:G10,A11:C15").Find(1, lookat:=xlWhole)

basarasy

先謝謝2位大大.
但我想找的是,在A1:C5,E6:G10,A11:C15 只有1個儲存格的值是1的數,多過1個儲存格的值是1的就
不出MSGBOX.
數值1是變數.

GBKEE

回復 7# basarasy
是這樣嗎?

1. Sub Ex()
   
2.     Dim X, E As Range, i%, Rng As Range
   
3.     X = 1  'X-> 數值的變數
   
4.     For Each E In Range("A1:C5,E6:G10,A11:C15")
   
5.         If E = X Then
   
6.             i = i + 1
   
7.             Set Rng = E
   
8.             If i >= 2 Then Exit For
   
9.         End If
   
10.     Next
    
11.     If i = 1 Then MsgBox "Row " & Rng.Row & " , Column " & Rng.Column
    
12. End Sub

複製代碼

basarasy

回復 8# GBKEE
是這個了.
非常感謝GBKEE大大.
還有個小問題是如何1次過把所有的 變數=Nothing?

GBKEE

回復 9# basarasy
如何1次過把所有的 變數=Nothing?
可以再說明白些嗎?