麻辣家族討論版版 - Powered by Discuz! Board

標題: match 搜尋不同工作表可以指定變數嗎? [打印本頁]

作者: EGBT 時間: 2015-9-1 15:11 標題: MATCH高手請進

陣列表A ~ 表F
數值表G ~ 表Z

VBA使用MATCH

要怎麼讓"數值"去比對"陣列"...

作者: ikboy 時間: 2015-9-1 17:07

=Application.match(lookup_value,要match的陣列,match_type))

Lookup_value: 可以是數值或文字
Match_type 為可選數 -1，0，或1。

Match_type參數指定將Excel匹配lookup_value的與值查找的陣列。此參數的默認值是1。下面描述Match_type用法。

1或省略 match認為是小於或等於最大值lookup_value的。在值查找的陣列參數必須被放置在上升順序，例如：...- 2，-1，0，1，2，...，AZ，FALSE，TRUE。
0 Match找到第一個值，該值正好等於lookup_value的。在中值查找的陣列參數可以是任意順序。
-1 Match發現的最小值大於或等於lookup_value的。在值查找的陣列參數必須被放置在遞減順序，例如：TRUE，FALSE ZA，... 2，1，0，-1，-2，...，等等。

作者: EGBT 時間: 2015-9-1 23:41

回復 2# ikboy

雖然不是我想要的答案,但很謝謝你的回覆...

作者: GBKEE 時間: 2015-9-2 08:07

回復 3# EGBT
是這樣嗎?

Option Explicit
Sub EX()
Dim 數值(), 陣列(), e As Variant, M As Variant
數值 = Array(0, "a" , 100 , "bb", 88)
陣列 = Array(88, "cc", 0, "a", "bb")
For Each e In 數值
M = Application.Match(e, 陣列, 0)
Debug.Print e, M '指令:檢視->即時運算視窗
Next
End Sub

複製代碼

作者: EGBT 時間: 2015-9-2 10:21

回復 4# GBKEE

我可以請問一下數值的Array跟陣列的Array是什麼..?

作者: EGBT 時間: 2015-9-2 10:58

回復 4# GBKEE

我應該問的不夠好...

應該說MATCH的數值跟陣列

數值與陣列分別在不同的Sheet

而數值跟陣列都有很多個Sheet

MATCH寫不好...

作者: EGBT 時間: 2015-9-2 11:08

回復 5# EGBT

如附檔

3個數值的Sheet

3個陣列的Sheet

然後每個數值的Sheet都要跑陣列的Sheet

作者: ikboy 時間: 2015-9-2 12:36

Sub zz()
Dim no_ar, arr, sh_ar
sh_ar = [{"數值1","陣列1";數值2","陣列2";數值3","陣列3"}]
For i = 1 To UBound(sh_ar)
With Sheets(sh_ar(i, 1))
      Set no_ar = .Range("a1:b" & .Cells(Rows.Count, 1).End(3).Row)
         arr = Sheets(sh_ar(i, 2)).[a1].CurrentRegion
         For j = 1 To no_ar.Rows.Count
            no_ar(j, 2) = Application.Match(no_ar(j, 1), arr, 0)
                  Next: End With: Next
Set no_ar = Nothing
End Sub

作者: EGBT 時間: 2015-9-2 14:23

回復 8# ikboy

很感謝你的回覆~這樣我有更多的問題想要請教你了..

作者: EGBT 時間: 2015-9-2 14:38

回復 8# ikboy

sh_ar = [{"數值1","陣列1";數值2","陣列2";數值3","陣列3"}]

我想請問一下 [{}] 這是什麼?

作者: EGBT 時間: 2015-9-2 14:43

回復 8# ikboy

還有一個問題是

打Application.WorkSheetFunction.Match...跟打Application.Match...有什麼不一樣?

作者: ikboy 時間: 2015-9-2 16:18

簡寫的常數二維陣列, 即 = Application.Transpose(Application.Transpose(Array(Array("數值1","陣列1"), Array("數值2","陣列2"), Array("數值3","陣列3"))))

作者: ikboy 時間: 2015-9-2 16:27

打Application.WorkSheetFunction.Match...跟打Application.Match...有什麼不一樣?
又是簡寫

作者: EGBT 時間: 2015-9-2 22:35

回復 12# ikboy

天啊,很感謝你的回答~!

作者: GBKEE 時間: 2015-9-3 05:59

回復 11# EGBT

回復 13# ikboy

參考這裡
http://forum.twbts.com/thread-10339-1-1.html

作者: ikboy 時間: 2015-9-3 07:40

多謝GBKEE版主指教, 懂了。

作者: EGBT 時間: 2015-9-3 11:22

回復 15# GBKEE

GBKEE大大您好

我的MATCH問題如下..(雖然很多大大都有回答但還是不太能夠讓我理解><)

假設x1為1~n個sheet (但不知道有幾個sheet),每個sheet裡面都有不規則數值,這裡就是放在Match的數值

假設y1為1~n個sheet (但不知道有幾個sheet),每個sheet裡面都有不規則的陣列數,這裡就是放在Match的陣列

我用粗糙的方法去計算有幾個x1和裡面的數值以及計算y1和裡面的陣列數

這樣不知道Match...附上檔案好像也說不太明白

作者: GBKEE 時間: 2015-9-3 14:43

回復 17# EGBT
試試看你的附檔程式碼

Option Explicit
Sub Ex()
Dim 數值表 As Variant, 陣列表 As Variant, E As Variant, EE As Variant
Dim Ar(), Ar1(1 To 2), Ar2(), i As Integer, M As Variant
'假設x1為1~n個sheet (但不知道有幾個sheet),由下面程式碼取得
For Each E In Sheets
If E.Name Like "數值*" Then 數值表 = 數值表 & "," & E.Name '字串:集合數值表
If E.Name Like "陣列*" Then 陣列表 = 陣列表 & "," & E.Name '字串:集合陣列表
Next
'*******************************************
數值表 = Split(Mid(數值表, 2), ",") '陣列: 字串以","分割為陣列
陣列表 = Split(Mid(陣列表, 2), ",")
ReDim Ar2(0) '重置陣列
For Each E In Sheets(數值表) '工作表陣列(數值表)
'每一數值表
Ar = E.Range("A:A").SpecialCells(xlCellTypeConstants).Value '數值資料置入陣列
Ar = Application.WorksheetFunction.Transpose(Ar) '陣列:二維(橫)轉一維(直)
For Each EE In Sheets(陣列表) '工作表陣列(陣列表)
'每一陣列表
For i = 1 To UBound(Ar) '數值資料陣列
M = Application.Match(Ar(i), EE.Range("A:A"), 0) '每一陣列表中Match數值
Ar1(1) = E.Name & " -" & Ar(i)
If IsError(M) Then
Ar1(2) = EE.Name & " - 找不到"
Else
Ar1(2) = EE.Name & " -A" & M
End If
Ar2(UBound(Ar2)) = Ar1
ReDim Preserve Ar2(UBound(Ar2) + 1) '陣列重置元素索引值,Preserve(保留原有元素)
Next
Next
Next
ReDim Preserve Ar2(UBound(Ar2) - 1) '陣列轉置時如子元素有為陣列,其他子元素須為相同大小的陣列
Sheets("結果").Range("A1").Resize(UBound(Ar2) - 1, 2) = Application.Transpose(Application.Transpose(Ar2))
End Sub

複製代碼

作者: EGBT 時間: 2015-9-3 15:33

回復 18# GBKEE

天啊..神人一枚!!!

小弟　Orz...

深思中!!

作者: EGBT 時間: 2015-9-3 16:55

回復 18# GBKEE

我想要請問大大,

Dim 數值表 As Variant, 陣列表 As Variant, E As Variant, EE As Variant
Dim Ar(), Ar1(1 To 2), Ar2(), i As Integer, M As Variant
'假設x1為1~n個sheet (但不知道有幾個sheet),由下面程式碼取得
For Each E In Sheets
If E.Name Like "數值*" Then 數值表 = 數值表 & "," & E.Name '字串:集合數值表
If E.Name Like "陣列*" Then 陣列表 = 陣列表 & "," & E.Name '字串:集合陣列表
Next
'*******************************************
數值表 = Split(Mid(數值表, 2), ",") '陣列: 字串以","分割為陣列
陣列表 = Split(Mid(陣列表, 2), ",")
ReDim Ar2(0) '重置陣列
For Each E In Sheets(數值表) '工作表陣列(數值表)

複製代碼

上面的「Dim 數值表 As Variant」他不是只是將「數值表」宣告成Variant

為什麼在下方「If E.Name Like "數值*" Then 數值表 = 數值表 & "," & E.Name」，這感覺有點像把相似「數值」的Sheet放入到數值表裡面，不是陣列也可以這麼做嗎?

還有下方的「For Each E In Sheets(數值表)」將數值表放到「E」裡面..數值表它不是只是普通的變數嗎? 怎麼可以把它變成像是陣列呢?

我是覺得...類似Sheets(數值(S)) <- 是放「數值(S)」陣列，將陣列放到E...

麻煩大大開示

作者: GBKEE 時間: 2015-9-4 06:02

回復 20# EGBT

Sub Ex()
Dim 數值表 As Variant, 陣列表 As Variant, E As Variant
'Variant 資料型態是所有沒被明確宣告為其他型態,可以為認何的資料型態
For Each E In Sheets
If E.Name Like "數值*" Then
數值表 = 數值表 & "," & E.Name '字串:集合數值表
MsgBox VarType(數值表) '8 (字串 )
End If
Next
'*******************************************
'Split : 字串以 "," (任何指定的字串) 分割而成為陣列
數值表 = Split(Mid(數值表, 2), ",")
'*******************************************
MsgBox VarType(數值表) '8192 (陣列) + 8 (字串 )
For Each E In Array("數值1", "數值2", "數值3") '工作表陣列(數值表)
MsgBox VarType(E) '8 (字串 )
Next
'For Each E In Sheets(Array("數值1", "數值2", "數值3")) '工作表陣列(數值表)
For Each E In Sheets(數值表) '工作表陣列(數值表)
MsgBox VarType(E) ' 9 物件
Next
End Sub

複製代碼

作者: EGBT 時間: 2015-9-4 08:44

回復 21# GBKEE

感恩大大的細心耐心開示!

作者: EGBT 時間: 2015-9-4 10:43

回復 21# GBKEE

大大..小弟在字串集合表不太懂...字串集合表他是把與"數值"相似的放進去「數值表」裡面嗎?

但「數值表」他不是宣告Variant嗎? 他可以連續放Sheet字串嗎?

不是要像宣告動態陣列例如宣告「數值表()」嗎？

作者: EGBT 時間: 2015-9-4 10:47

回復 21# GBKEE

大大我明白了...他是用「,」分隔開來...我懂了...

作者: EGBT 時間: 2015-9-4 13:16

回復 21# GBKEE

大大想再請教一下,

上面有一行是「數值表 = Split(Mid(數值表, 2), ",")」

這行可以解釋一下嗎...數值表有可能會等於「數值、數值1、數值2....數值123456」假設前面的If ... Like "數值*******"

你這行「數值表 = Split(Mid(數值表, 2), ",")」可以解釋給小弟理解嗎...

還有一個問題就是，當整個字串成為不規律且將字串連結起來中間用逗號隔開「台灣,中國,俄羅斯,美國...」，我要怎麼遇到逗號就切割一個字串出來?

作者: GBKEE 時間: 2015-9-5 07:25

回復 25# EGBT

Option Explicit
Sub Ex()
Dim 數值表 As Variant, e As Variant
數值表 = "數值甲%A#數值1%A#數值2%A#數值123456%A#數值abc"
'*******************************************
'Split : 以所指定的字串, 分割而成為陣列
數值表 = Split(數值表, "%A#")
'*******************************************
For Each e In 數值表
MsgBox e
Next
End Sub

複製代碼

作者: EGBT 時間: 2015-9-5 12:37

回復 26# GBKEE

好喔謝謝大大

作者: EGBT 時間: 2015-9-19 16:10

回復 26# GBKEE

哈囉 GBKEE 大大~

若是這個寫法"數值"與"陣列"比較非單純數值，若是日期呢？

作者: EGBT 時間: 2015-9-19 18:00

回復 26# GBKEE

關於前個問題的Match Date

將 M = Application.Match(Ar(i), ProductRun.Range("A:A"), 1)

=> M = Application.Match(CLng(CDate(Ar(i))), ProductRun.Range("A:A"), 1)

謝謝你囉

作者: EGBT 時間: 2015-9-19 18:40

回復 26# GBKEE

GBKEE大大您好...很不好意思,我突然有一個問題

假設 "數值1" 在 "陣列1" 做Match

比對出來是好幾列,

那我要怎麼抓出比對後的列數，在B欄的內容?

例如：比對出來100列 (A100)，那可以抓出 (B100) 的內容嗎?

作者: EGBT 時間: 2015-9-19 18:50

回復 26# GBKEE

抓取數值

我是直接改

EE.Activate，激活Sheet去抓取，不知道GBKEE大大是否有更活的方法呢?

作者: GBKEE 時間: 2015-9-20 07:10

假設 "數值1" 在 "陣列1" 做Match
比對出來是好幾列,那我要怎麼抓出比對後的列數，在B欄的內容?

請參考第18樓的程式碼

我是直接改
EE.Activate，激活Sheet去抓取，
EGBT 發表於 2015/9/19 18:50

不需 EE.Activate,EE.Select 移動活頁簿的工作表

With EE
.Range("A1")="XXX"
End With

複製代碼

作者: citizen0923 時間: 2015-9-22 23:54 標題: match 搜尋不同工作表可以指定變數嗎?

如主題:match 搜尋不同工作表可以指定變數嗎?
假設~
工作表AAA裡面A欄有
AA001
AA002

工作表BBB裡面A欄有
BB001
BB002

工作表CCC裡面想使用MATCH
A1欄為AAA
A2欄為BBB
A3欄為搜尋值(假設為AA001)
A4為MATCH的結果

執行Cells(4, 1) = [MATCH(A3,AAA!A1:A20,0)]
可試出 AA001 在 AAA表中的第1個位置。
若想把當中的AAA!改成變數的方式，請問該如何修改?

例如改成
Cells(4, 1) = [MATCH(A3,Worksheets(Cells(1, 1)A1:A20,0)]
就會出現錯誤了~ 有點煩惱
還望前輩指點迷津，有勞費心了，謝謝

[attach]22055[/attach]

作者: GBKEE 時間: 2015-9-23 06:43

回復 1# citizen0923
參考這裡
http://forum.twbts.com/thread-14979-1-1.html

http://forum.twbts.com/thread-2811-1-1.html

作者: EGBT 時間: 2015-9-23 12:54

回復 32# GBKEE

WoW...Cool~

但是我還有一個問題...就是我是比對日期

比對的數值為
2015/1/1

比對的陣列為
2014/12/31
2015/1/2
2015/1/3
...
..
.

Match後面設定 1 找小於會找到 2014/12/13

但是我設定 -1 找大於他出現 "溢位" 而不會找到 2015/1/2 ...

問題出在哪裡?

作者: GBKEE 時間: 2015-9-23 14:45

回復 33# EGBT

Option Explicit
Sub Ex()
' Dim I As Integer, AR() '這樣會溢位
'Integer 變數係以範圍為 -32,768 到 32,767 之 16 位元 (2 個位元組) 數字的形式儲存
Dim I As Single, AR()
'Single (單精度浮點數) 變數係以 IEEE 32 位元 (4 個位元組) 浮點數字的形式儲存，
'它的範圍在負數的時候是從 -3.402823E38 到 -1.401298E-45，
'而在正數的時候是從 1.401298E-45 到 3.402823E38。Single 的型態宣告字元為 !。
ReDim AR(1 To Rows.Count)
AR(Rows.Count) = #1/1/2015#
I = Application.Match(#2/1/2015#, AR, -1)
MsgBox I
End Sub

複製代碼

作者: EGBT 時間: 2015-9-23 19:53

回復 34# GBKEE

再請問GBKEE大大

Integer範圍為 -32,768 到 32,767 之 16 位元會溢位,

而Single負數的時候是從 -3.402823E38 到 -1.401298E-45

正數的時候是從 1.401298E-45 到 3.402823E38，為什麼Single就不會溢位呢？我還是不太懂這個道理...

ReDim AR(1 To Rows.Count)
AR(Rows.Count) = #1/1/2015# ' <- 不能寫死，Match就是-1，抓不到的時候才會找大於自己的日期
I = Application.Match(#2/1/2015#, AR, -1)
MsgBox I

哈~很感謝GBKEE大大這麼耐心的回覆解答啊~

作者: EGBT 時間: 2015-9-23 20:59

回復 34# GBKEE

例如附檔~

這樣子好像也是 #N/A、錯誤2048 ...哈....可能我還沒辦法領悟GBKEE大大所說的

Sub test()
Dim i, y, x As Single
i = Application.Match("A1", Range("C1:C14"), -1)
Range("B1") = i
y = Application.Match("A2", Range("C1:C14"), -1)
Range("B2") = y
x = Application.Match("A3", Range("C1:C14"), -1)
Range("B3") = x
End Sub

複製代碼

作者: citizen0923 時間: 2015-9-23 23:49

G大您好~這兩篇我都有看過了喔~ 不過能理解有限，
大概知道[]中不接受變數的字串，所以把程式碼改成
Private Sub CommandButton1_Click()
Cells(4, 1) = Application.Match(A3, Sheets("AAA").Range("A1:A10"), 0)
End Sub
'目標是透過目前工作表CCC的A3欄資料(AA001)，去match工作表AAA中A1~A10符合AA001的位置後，寫在工作表CCC的Cells(4, 1)位置
如此還是會找不到目標喔~ 能幫忙指點嗎~~ 不好意思了

作者: GBKEE 時間: 2015-9-24 05:43

回復 3# citizen0923

Private Sub CommandButton1_Click()
'Cells(4, 1) = Application.Match(A3, Sheets("AAA").Range("A1:A10"), 0)
Cells(4, 1) = Application.Match([A3], Sheets("AAA").Range("A1:A10"), 0)
Cells(4, 1) = Application.Match(Range("A3"), Sheets("AAA").Range("A1:A10"), 0)
End Sub

複製代碼

作者: GBKEE 時間: 2015-9-24 06:00

回復 38# EGBT
401298E-45 到 3.402823E38 是科學記號,請GOOGLE 查看了解
Dim i, y, x As Single
'i, y 沒指定都是 As Variant,V請自行查看 VBA 的說明
i, y 程式可以接受傳回錯誤值,
x As Single 必須是傳回數字,程式不接受錯誤值.

Application.Match("A1", Range("C1:C14"), -1)

複製代碼

請看40樓的程式碼

作者: EGBT 時間: 2015-9-24 13:40

回復 41# GBKEE

Match回傳是數字(列)沒錯..但我的程式碼如下

Match打 1 or -1 都不行...打0就可以但找不到相同的數值他會找"小於"數值的，卻不是找"等於"數值的

附檔案

Sub test()
Dim x As Single
x = Application.Match(Range("A1"), Range("B:B"), -1)
Sheets("Sheet1").Range("F1") = x
End Sub

複製代碼

作者: GBKEE 時間: 2015-9-24 15:31

本帖最後由 GBKEE 於 2015-9-24 15:37 編輯

回復 42# EGBT
x As Single (是針對溢位修改的型態 )
Match 找不到傳回錯誤值,與x的型態不對,故程式有型態不符合的誤值.
所以必須是 Dim x As Variant

Match打 1 or -1 都不行...打0就可以
應該是 Match打 0 or -1 都不行...打1就可以

Match_type 是個數字，其值有三種可能：-1、0 或 1。用以指定 Microsoft Excel 如何從 lookup_array 裡尋找 lookup_value。
如果 match_type 是 1，則 MATCH 函數會找到等於或僅次於 lookup_value 的值。Lookup_array 必須以遞增次序排列：...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。
如果 match_type 是 0，則 MATCH 函數會找第一個完全等於 lookup_value 的比較值。Lookup_array 可以依任意次序排列。
如果 match_type 是 -1，則 MATCH 函數會找到等於或大於 lookup_value 的最小值。Lookup_array 必須以遞減次序排序：TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ...，以此類推。
如果 match_type 引數被省略，則假設其值為 1。

複製代碼

作者: EGBT 時間: 2015-9-24 16:55

回復 43# GBKEE

如果 match_type 是 -1，則 MATCH 函數會找到等於或大於 lookup_value 的最小值。Lookup_array 必須以遞減次序排序：TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ...，以此類推。

關鍵就是在...要遞減排序...我看Match說明

只有打到match_type 為 1 , Lookup_array 必須以遞增排列...

我想請問一下...假如說 match_type = -1 (找遞減)

數值：2015/1/1

陣列：
2015/1/12
2014/12/20
2014/12/10
2014/12/5

以 match_type = -1 會找到 2014/12/20

我能控制在 5日內嗎? 也就是說找2014/12/25 ~ 2014/12/29 假如沒有就是找不到

作者: citizen0923 時間: 2015-9-24 19:44

回復 citizen0923
GBKEE 發表於 2015-9-24 05:43

謝謝G大~ 我試出來了!!
Private Sub CommandButton1_Click()
Dim sheetx As String
sheetx = Cells(1, 1)
Cells(4, 1) = Application.Match(Range("A3"), Sheets(sheetx).Range("A1:A10"), 0)
End Sub
改成這樣~就可以自由的更改搜尋的分頁名稱了~ 再次感謝您~!!謝謝

作者: GBKEE 時間: 2015-9-25 08:31

回復 44# EGBT

match_type 是 1，則 MATCH 函數會
找到等於或僅次於 lookup_value 的值。Lookup_array 必須以遞增次序排列：...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。
match_type 是 -1，則 MATCH 函數會
找到等於或大於 lookup_value 的最小值。Lookup_array 必須以遞減次序排序：TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ...，以此類推。
如果 match_type 引數被省略，則假設其值為 1

Sub test()
Dim x As Variant, Mdate As Date
x = Application.Match(Range("A1"), Range("B:B"), -1)
If IsNumeric(x) Then
Mdate = Range("b" & x)
x = Range("A1") - Mdate
If x >= 0 And x <= 5 Then MsgBox Mdate
End If
End Sub

複製代碼

作者: EGBT 時間: 2015-9-25 21:38

回復 46# GBKEE

大大求救..為什麼會出現"載入DLL時發生錯誤"

工具 -> 設定引用項目都有打勾

我檔案都有備份，只是我想知道為什麼會出現"載入DLL時發生錯誤"...

但不是因為程式碼才導致"載入DLL時發生錯誤"的發生，因為我試過很次.....

作者: GBKEE 時間: 2015-9-26 09:00

回復 47# EGBT
關閉檔案(要存檔).再開啟看看

作者: EGBT 時間: 2015-9-26 15:07

回復 48# GBKEE

基本上打開出現"載入DLL時發生錯誤"

這時候不管存檔與否，程式都已經消失不見了

作者: EGBT 時間: 2015-10-2 20:41

回復 48# GBKEE

請問GBKEE大大

關於Combobox下拉是選單的List

清單內容能不能從程式碼打好預設，而不是從儲存格?

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)