不用迴圈，不用Worksheet函數（match等），判斷某element在是否陣列中

sunnyso

例如

1. Sub ss_test1()
   
2.     alltype = Array("A類", "B類", "C類", "D類", "E類", "F類", "G類", "H類", "I類", "J類")
   
3.     aj = "1D類"
   
4.     If UBound(Filter(alltype, aj)) < 0 Then Debug.Print "Empty"
   
5.     Stop
   
6. End Sub

複製代碼

sunnyso

回復 1# sunnyso

判斷該element在陣列中位置
Debug.Print UBound(Split(Split(Join(alltype, "|"), aj)(0), "|")) + 1

1.     If UBound(Filter(alltype, aj)) < 0 Then
   
2.         Debug.Print "Empty"
   
3.     Else
   
4.         Debug.Print UBound(Split(Split(Join(alltype, "|"), aj)(0), "|")) + 1
   
5.     End If

複製代碼

sunnyso

回復  sunnyso

判斷該element在陣列中位置
Debug.Print UBound(Split(Split(Join(alltype, "|"), aj)( ...
sunnyso 發表於 2013-6-30 23:11

樓上有bug，改爲

1.     If UBound(Filter(alltype, aj)) < 0 Then
   
2.         Debug.Print "Empty"
   
3.     Else
   
4.         Debug.Print UBound(Split(Split(“ ” & Join(alltype, "|"), aj)(0), "|"))
   
5.     End If

複製代碼

GBKEE

回復 3# sunnyso
判斷完全等於該element在陣列中位置

1. Sub Ex()
   
2.     Dim S As String, alltype(), Aj As String, Ar  As String
   
3.     S = ","                           '輔助字串
   
4.     alltype = Array("A類", "B類", "DEC類", "EC類", "C類", "F類", "G類", "H類", "I類", "J類")
   
5.     Aj = S & "C類" & S
   
6.     Ar = S & Join(alltype, S) & S
   
7.     If InStr(Ar, Aj) = 0 Then
   
8.         Debug.Print "Empty"
   
9.     Else
   
10.        Ar = Split(Ar, Aj)(0)
    
11.        Debug.Print IIf(UBound(Split(Ar, S)) < 0, 0, UBound(Split(Ar, S)))
    
12.     End If
    
13. End Sub

複製代碼

sunnyso

回復 4# GBKEE


版大你的code有bug，跟我第一次的code（2樓）一樣的bug。就是第一element（即: "A類") 回傳 -1
應該像我3樓的改動（或者看我另一帖子）才可以，在Join 後的 AllType前面加上一個任意非 S（輔助字串）的字串，例如“<space>", 不然第一個emement會回傳-1， 還有在Join 後的 AllType後面並不需要串輔助字串

改動後如下

1. Sub ss_teswt()
   
2.     alltype = Array("A類", "B類", "C類", "D類", "E類", "F類", "G類", "H類", "I類", "J類")
   
3.     Aj = "J類"
   
4.     If UBound(Filter(alltype, Aj)) < 0 Then
   
5.         Debug.Print "Empty"
   
6.     Else
   
7.         Debug.Print UBound(Split(Split(" " & Join(alltype, "|"), Aj)(0), "|"))
   
8.     End If
   
9.     'Stop
   
10. End Sub

複製代碼

GBKEE

回復 5# sunnyso
謝謝指正有錯當改 , 4#已修正.

可是如這樣還是有bug 找到 "JJ類"

1. Sub ss_teswt()
   
2.     alltype = Array("A類", "B類", "C類", "D類", "JJ類", "F類", "G類", "H類", "I類", "J類")
   
3.     Aj = "J類"
   
4.     If UBound(Filter(alltype, Aj)) < 0 Then
   
5.         Debug.Print "Empty"
   
6.     Else
   
7.         Debug.Print UBound(Split(Split(" " & Join(alltype, "|"), Aj)(0), "|"))
   
8.     End If
   
9.     'Stop
   
10. End Sub

複製代碼

sunnyso

回復 6# GBKEE

的確，再想想有沒有更好的方法

sunnyso

回復 4# GBKEE

這樣可以少一IF判斷

1. Sub Ex()
   
2.     Dim S As String, alltype(), Aj As String, Ar  As String
   
3.     S = ","                           '輔助字串
   
4.     alltype = Array("DA類", "B類", "DEC類", "EC類", "C類", "F類", "G類", "H類", "I類", "J類")
   
5.     Aj = S & "DA類" & S
   
6.     Ar = " " & S & Join(alltype, S)
   
7.     If InStr(Ar, Aj) = 0 Then
   
8.         Debug.Print "Empty"
   
9.     Else
   
10.        Ar = Split(Ar, Aj)(0)
    
11.        Debug.Print UBound(Split(Ar, S))
    
12.     End If
    
13. End Sub

複製代碼

GBKEE

回復 8# sunnyso
在此討論區就是要有如此的交流精神,謝謝你

sunnyso

回復 9# GBKEE

6樓的code適合以關鍵字來判斷element的位置