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

標題: "Vlookup" and "Match" in VBA [打印本頁]

作者: am0251 時間: 2011-6-21 16:11 標題: "Vlookup" and "Match" in VBA

我平常都會用很多"Vlookup" 跟 "Match",如果我想用VBA的方式寫出來,該怎麼做呢?

作者: oobird 時間: 2011-6-21 17:06

http://forum.twbts.com/viewthrea ... p;extra=&page=1
這裡有在vba中引用工作表函數的實例

作者: am0251 時間: 2011-6-22 13:31

謝謝回覆,可惜我權限不夠,不能下載,可以簡單的介紹一下嗎?

作者: oobird 時間: 2011-6-22 13:38

方法1：用application.引用公式
方法2.用evaluate+公式
方法3.用中刮號把公式框起來

作者: am0251 時間: 2011-6-22 14:12

謝謝,我試了...
x = Range("D" & z).Application.Formula = " [=MATCH(Sheets1!""ItemName"",Sheets2!A:A,0)]"
就是不行,可以告訴我,我錯在哪裡嗎?
謝謝~~!

作者: oobird 時間: 2011-6-22 15:15

Range("d" & z) = Evaluate("MATCH(""ItemNam"",Sheet2!A:A,0)")

作者: oobird 時間: 2011-6-22 15:20

上述列出的方法你只能選一樣
不能每樣同時用，還加上錄製的代碼
前面"x="也不能加在裡面，哪能這麼多等號？

作者: am0251 時間: 2011-6-22 16:36

本帖最後由 am0251 於 2011-6-22 17:13 編輯

很奇怪,我試過:
ItemNam = Range("A" & z).Value
x = Application.WorksheetFunction.Match(""ItemNam"",, Sheets("OUT").Range("A:A"))
是不行的,只有不用變數"ItemNam"而直接把"Range("A" & z).Value"打進去才能用[attach]6759[/attach]

作者: oobird 時間: 2011-6-22 17:14

變數有變數的用法，跟字符是不同的表示。
變數不用雙引號。

作者: oobird 時間: 2011-6-22 21:31

嗯，上傳檔案就好辦了！
改這樣

Sub test()
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim ItemName As String
z = 2
Do While Range("A" & z).Value <> ""
ItemNam = Worksheets("Sheet1").Range("A" & z).Value
x = Application.Match(ItemNam, Sheet2.[A1:A100], 0)
Debug.Print x
z = z + 1
Loop
End Sub

複製代碼

作者: am0251 時間: 2011-6-23 13:25

謝謝指教~~!!感覺功力提昇了不小!!

作者: GBKEE 時間: 2011-6-23 13:42

本帖最後由 GBKEE 於 2011-6-23 13:46 編輯

回復 8# am0251
Sub test()
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim ItemName As String
z = 2
Do While Range("A" & z).Value <> ""
'ItemNam = Worksheets("Sheets1").Range("A" & z).Value '你的附檔中沒有Sheets1工作表
'x = Evaluate("MATCH(""ItemNam"",Sheet(Sheets2)!A:A,0)") 你的附檔中沒有Sheets2工作表
'x = Application.WorksheetFunction.Match(Worksheets("Sheets1").Range("A" & z).Value, Sheets("Sheets2").Range("A:A")) '如上的錯誤
ItemNam = Worksheets("Sheet1").Range("A" & z).Value
x = Evaluate("MATCH(""" & ItemNam & """,Sheet2!A:A,0)") 'ItemNam是變數要用&來連接
x = Application.WorksheetFunction.Match(Worksheets("Sheet1").Range("A" & z).Value, Sheets("Sheet2").Range("A:A"))
Debug.Print x, y
z = z + 1
Loop
End Sub

作者: am0251 時間: 2011-6-23 13:57

本帖最後由 am0251 於 2011-6-23 15:03 編輯

如果我想把變數"y"的值等於,我的UserFrom 內的 Calendar 的值,可是我寫了"y = Calendar1.Value"出來的結果是"1/六月/2011 ",我的屬性已經改"英文(簡短)"的了,要在哪裡可以改成"1-Jun-11"呢?
因為我要把"x" "y"變成Cells(x,y)的痤標,"x"就是ItemName,"y"就是日期,如B2="1-Jun-11"; C2="2-Jun-11"............有可能做的到嗎?

作者: oobird 時間: 2011-6-23 15:47

y = Format(Calendar1.Value, "d-mmm-yy")
MsgBox y

作者: am0251 時間: 2011-6-23 16:52

不好意思......我又失敗了......[attach]6764[/attach]

作者: oobird 時間: 2011-6-23 20:26

Cells(x, y)裡面的x,y都必須是數字
不能是日期。

作者: am0251 時間: 2011-6-24 14:26

本帖最後由 am0251 於 2011-6-24 15:36 編輯

謝謝指點,已改好了,可是.......還是不行.........[attach]6782[/attach]

作者: lone_tiger0303 時間: 2011-6-25 10:52

剛好有利用到~~感謝oobird
Private Sub CommandButton1_Click()
Dim x As Integer, y As Integer, z As Integer, ItemName As String
z = 6
Do While Range("N" & z).Value <> ""
ItemNam = Sheet3.Range("N" & z).Value
Sheet3.Range("S" & z).Value = Application.VLookup(ItemNam, Sheet4.[A1:B29], 2, 0)
z = z + 1
Loop

End Sub

作者: am0251 時間: 2011-6-25 14:32

本帖最後由 am0251 於 2011-6-25 14:39 編輯

不好意思,小的沒看懂....這是我的程序

Private Sub Start_Click()
Dim x As Integer, y As Integer, z As Integer, S1 As Integer, S2 As Integer, myRowCount As Integer
Dim ShipDate As Date
Dim ItemName As String
z = 2
myRowCount = Range("A1").CurrentRegion.Rows.Count
ItemName = Worksheets("Sheet1").Range("A" & z).Value
ShipDate = Format(Calendar1.Value, "d-mmm-yy")
For i = 2 To myRowCount
x = Application.Match(ItemName, Sheet2.["A1", "A" & myRowCount], 0)
y = Application.Match(ShipDate, Sheet2.[B1:AF1], 0)
S1 = Workbooks("Book1").Worksheets("Sheet1").Cells(z, 2).Value
S2 = Workbooks("Book1").Worksheets("Sheet2").Cells(x, y).Value
S2 = S2 + S1
z = z + 1
Next i
Unload Me
MsgBox "Done"
End Sub

複製代碼

作者: Hsieh 時間: 2011-6-25 18:26

本帖最後由 Hsieh 於 2011-6-25 18:51 編輯

回復 19# am0251
x = Application.Match(ItemName, Sheet2.["A1", "A" & myRowCount], 0)
這是錯誤語法，改為
x = Application.Match(ItemName, Sheet2.Rnage("A1:A" & myRowCount), 0)

日期在工作表內被視為Double資料型態
所以應宣告為
Dim ShipDate As Double
這樣你的y值才會找的到

作者: am0251 時間: 2011-6-26 14:12

本帖最後由 am0251 於 2011-6-27 18:14 編輯

謝謝Hsieh大大的出手相助......可是還是不行.........,可以再告訴我,我又錯在哪裡呢?萬二分感謝!

Private Sub Start_Click()
Dim x As Integer, y As Integer, z As Integer, S1 As Integer, S2 As Integer, myRowCount As Integer
Dim ShipDate As Double
Dim ItemName As String
z = 2
myRowCount = Range("A1").CurrentRegion.Rows.Count
ShipDate = Format(Calendar1.Value, "d-mmm-yy")
For i = 2 To myRowCount
ItemName = Worksheets("Sheet1").Range("A" & z).Value
x = Application.Match(ItemName, Sheet2.Range("A1:A" & myRowCount), 0)
y = Application.Match(ShipDate, Sheet2.[B1:AF1], 0)
S1 = Workbooks("Book1").Worksheets("Sheet2").Cells(x, y).Value
S2 = Workbooks("Book1").Worksheets("Sheet1").Cells(z, 2).Value
S1 = S1 + S2
z = z + 1
Next i
Unload Me
MsgBox "Done"
End Sub

複製代碼

作者: Hsieh 時間: 2011-6-26 22:52

本帖最後由 Hsieh 於 2011-6-26 22:54 編輯

回復 21# am0251
既然已經宣告成DOUBLE型態
ShipDate = Format(Calendar1.Value, "d-mmm-yy")
這樣又轉成了字串，當然型態不符
直接ShipDate =Calendar1.Value
只針對語法解釋，其他不了解你的目的是甚麼?無法給你任何意見

作者: am0251 時間: 2011-6-27 14:03

是這樣的,我的設計是想把"SHEET1"的資料按日期,名稱,每頁的名稱,放到對應的位置,可惜功力不夠,千差萬錯,希望各位高手指點一下~~謝謝!![attach]6851[/attach]

作者: Hsieh 時間: 2011-6-28 10:06

回復 23# am0251

Private Sub Start_Click()
Dim mydate#
mydate = Calendar1.Value
Set d = CreateObject("Scripting.Dictionary")
With Sheet1
sh = .[E1] '工作表名稱
For Each a In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
d(a.Value) = a.Offset(, 1).Value
Next
With Sheets(sh)
k = Application.Match(mydate, .Rows(1), 0)
If IsError(k) Then MsgBox "The day is not find!": Exit Sub
For Each a In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
.Cells(a.Row, k) = d(a.Value)
Next
End With
End With
Unload Me
MsgBox "Done"
End Sub

複製代碼

作者: am0251 時間: 2011-6-28 11:41

完美的解決~~!!不過可以再教我多一個功能嗎?就是想每次把資料送到目標位置時不用"="而是相加,例如,"SHEET1" 的"AAA"的資料是"1",但目標位置本來就有"2"的話,運作之後就可以變成"3",那就是我的目了..謝謝!!

作者: Hsieh 時間: 2011-6-28 14:22

.Cells(a.Row, k) = .Cells(a.Row, k) +d(a.Value)

作者: ten999 時間: 2011-7-12 11:38

可查看excel說明內公式查詢

作者: Happkkevin 時間: 2011-7-17 01:31

看了上面的帖子，對於函數使用於VBA 還是一知半解
不知可否將常用的函數組合運用於 VBA 呈現，配合自定義函數使用
但應該是對於資料形態還不了解，再加上函數的寫法更是剛用
所以寫了一個應該是錯誤不少，改了數次都亂了譜
如附件，希望有先進可以協助一下看是否可行
函數組合為
=IF(ISNA(VLOOKUP($A2,設備清單!$A$1:$B$20,MATCH(D$1,設備清單!A$1:B$1,0),0)),"無設備資料",(VLOOKUP($A2,設備清單!$A$1:$B$20,MATCH(D$1,設備清單!A$1:B$1,0),0)))
希望呈現
=vlmatch($A2,E$1,無設備資料,設備清單!$A$1:$B$20,設備清單!$A$1:$B$1)
vlmatch 為自定義函數，只需輸入 $A2,E$1,無設備資料,設備清單!$A$1:$B$20,設備清單!$A$1:$B$1即可
改了數次，最後錯誤，寫了如下

Public Function VLMatch(tt$, KK$, NN$, DALL As Range, dt As Range) As String
Dim i%, j$, k As Boolean
i = Evaluate("Match(kk, dt, 0)")
j = Evaluate("VLookup(tt, DALL, i, 0)")
k = Evaluate("IsNA(j)")
If k = False Then
VLMatch = NN
Else
VLMatch = j
End If
End Function

複製代碼

[attach]7031[/attach]

作者: GBKEE 時間: 2011-7-17 08:45

回復 28# Happkkevin

Public Function VLMatch(tt$, KK$, NN$, DALL As Range, dt As Range) As String
'Match 找到值則傳回數字,沒找到值則傳回錯誤值
'VLookup 找到傳回字串,沒找到傳回錯誤值
Dim i As Variant, j As Variant '變數需設定為Variant資料型態
'Variant資料型態是所有沒被明確宣告為任一其他型態
i = Application.Match(KK, dt, 0) 'vba中使用工作表函數-> Application.工作表函數
'請看vba中可使用的工作表函數
j = Application.VLookup(tt, DALL, i, 0)
'*** Evaluate("VLookup(tt, DALL, i, 0)")=[VLookup(tt, DALL, i, 0)]
'不接受變數 tt, DALL, i ***
If IsError(j) Then 'j傳回錯誤值
VLMatch = NN
Else: 'j傳回字串
If j = "" Then VLMatch = NN Else VLMatch = j
End If
End Function

複製代碼

作者: Happkkevin 時間: 2011-7-17 11:58

本帖最後由 Happkkevin 於 2011-7-17 12:00 編輯

回復 Happkkevin
GBKEE 發表於 2011-7-17 08:45

GBKEE 版大，真的是太厲害囉
每每我花了許久時間修正只要經過此處先進的指導
不只問題解決，更讓我學到許多
至於VBA可用函數之前我沒有確認只是 if 發生問題才處理
說明中我的軟體關於 vlookup 與 match 沒有說明，但我想應該是與函數相同
感謝你的指導
修正為你的語法加上修正 NN輸入""即符合我的需求
=vlmatch($A2,E$1,"無設備資料",設備清單!$A$1:$B$20,設備清單!$A$1:$B$1)

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