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

標題: [分享] VBA分享-效果蠻類似VLOOKUP查詢,只是可以兩欄互查 [打印本頁]

作者: infoverdad 時間: 2011-7-14 19:29 標題: VBA分享-效果蠻類似VLOOKUP查詢,只是可以兩欄互查

本帖最後由 infoverdad 於 2011-7-14 19:31 編輯

分享之前參考的VBA程式碼

效果蠻類似VLOOKUP查詢,只是可以兩欄互查。
B欄與C欄皆為資料驗證"清單"的方式
點選任一欄位, 則其對應的值會自動帶入
[attach]7012[/attach]
[attach]7013[/attach]

作者: GBKEE 時間: 2011-7-18 20:23

回復 1# infoverdad
不錯的分享,另用 Find 方法分享.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsLists As Worksheet, Rng As Range
If Target.Count > 1 Then Exit Sub
Set wsLists = Worksheets("Lists")
Application.EnableEvents = False
If Target.Column = 2 Then
Set Rng = wsLists.Range("A:A").Find(Target, LookAt:=xlWhole, MatchCase:=True)
With Target.Offset(0, 1)
If Not Rng Is Nothing Then .Value = Rng.Offset(0, 1).Value Else .Value = ""
End With
ElseIf Target.Column = 3 Then
Set Rng = wsLists.Range("B:B").Find(Target, LookAt:=xlWhole, MatchCase:=True)
With Target.Offset(0, -1)
If Not Rng Is Nothing Then .Value = Rng.Offset(0, -1).Value Else .Value = ""
End With
End If
Application.EnableEvents = True
End Sub

複製代碼

作者: infoverdad 時間: 2011-7-18 22:21

本帖最後由 infoverdad 於 2011-7-18 22:25 編輯

回復 2# GBKEE

謝謝GBKEE版大,真是太棒了!! 它不但更簡潔,也解決了我之前因應用需要,須逐一修改定義名稱的麻煩!!
不過為避免修改欄名，而致另一欄欄名變空白, 還是要使用exitHandler來處理它. 所以我把程式碼稍微修改了一下,這樣我未來在工作上就可以多多善用它了!!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsLists As Worksheet, Rng As Range
On Error GoTo errHandler
If Target.Count > 1 Then Exit Sub
Set wsLists = Worksheets("Lists")
Application.EnableEvents = False
If Target.Column = 2 Then
Set Rng = wsLists.Range("A:A").Find(Target, LookAt:=xlWhole, MatchCase:=True)
With Target.Offset(0, 1)
If Not Rng Is Nothing Then .Value = Rng.Offset(0, 1).Value Else: GoTo exitHandler
End With
ElseIf Target.Column = 3 Then
Set Rng = wsLists.Range("B:B").Find(Target, LookAt:=xlWhole, MatchCase:=True)
With Target.Offset(0, -1)
If Not Rng Is Nothing Then .Value = Rng.Offset(0, -1).Value Else: GoTo exitHandler
End With
End If
Application.EnableEvents = True
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
GoTo exitHandler
End Sub

複製代碼

作者: Hsieh 時間: 2011-7-18 23:54

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim A As Range, c As Integer, s As Range
Application.EnableEvents = False
With Target.Validation
Set s = Evaluate(.Formula1)
End With
Set A = s.Find(Target, lookat:=xlWhole)
c = IIf(A.Column = A.CurrentRegion.Column, 1, -1)
Target.Offset(, c) = A.Offset(, c)
Application.EnableEvents = True
End Sub

複製代碼

作者: GBKEE 時間: 2011-7-19 07:27

本帖最後由 GBKEE 於 2011-7-19 08:15 編輯

回復 4# Hsieh超版
你的程式更簡潔
但 Set A = s.Find(Target, lookat:=xlWhole) 之後沒有設下 A Is Nothing 的條件,(這檔案的驗證清單範圍中沒有空白值)

作者: Hsieh 時間: 2011-7-19 08:17

在清單範圍找驗證選項肯定找的到
否則驗證就無法通過

作者: GBKEE 時間: 2011-7-19 08:46

本帖最後由 GBKEE 於 2011-7-19 08:48 編輯

回復 6# Hsieh超版
我的意思是有驗證清單的儲存格式是允許輸入空白值的,如輸入值是空白值,
Set A = s.Find(Target, lookat:=xlWhole) 傳回 A Is Nothing
你4樓的程式, 在樓主的檔案會產生錯誤.

作者: Hsieh 時間: 2011-7-19 08:58

回復 7# GBKEE

還是你心思細膩，這個判斷我原先是有寫進去
只是後來認為清單內的選項所以省略
這個判斷，想想還是必要，因為就算是驗證，還是容許清空內容

作者: infoverdad 時間: 2011-7-19 09:07

好感謝兩位超級版大的指導,等一下來用用看.

作者: infoverdad 時間: 2011-7-19 11:10

本帖最後由 infoverdad 於 2011-7-19 11:11 編輯

我自己修改了一下,不過不知可否再精簡?
另附上測試檔.[attach]7051[/attach]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim A As Range, c As Integer, s As Range
On Error GoTo errHandler
Application.EnableEvents = False
With Target.Validation
Set s = Evaluate(.Formula1)
End With
Set A = s.Find(Target, lookat:=xlWhole)
If Not A Is Nothing Then '檢查是否有內容被清空
c = IIf(A.Column = A.CurrentRegion.Column, 1, -1)
Target.Offset(, c) = A.Offset(, c)
Application.EnableEvents = True
Else '當有內容被清空時之處理
GoTo exitHandler
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
'MsgBox Err.Number & ": " & Err.Description
GoTo exitHandler
End Sub

複製代碼

作者: joeron 時間: 2011-12-30 07:55

感謝版大的分享，目前還是無法下載參考.. 只能透過分享的程式碼來測驗

作者: peter460191 時間: 2012-7-30 15:41

回復 10# infoverdad

請問 infoverdad

您附檔的test，若要把資料顯現方式，從『左右』改為『上下』時要如何改，研究了好久，想從 offset去改，可是找不地方可以改?可以指導一下嗎?

作者: xandertco 時間: 2012-8-16 21:23

感謝分享, 謝謝

作者: xandertco 時間: 2012-8-16 21:24

感謝分享, 謝謝

作者: dryadf 時間: 2014-5-1 02:12

我好想下載來看看，光是看代碼實在看不明白

作者: dryadf 時間: 2014-5-2 02:57

回復 infoverdad
不錯的分享,另用 Find 方法分享.
GBKEE 發表於 2011-7-18 20:23

GBKEE版大好
小弟是剛開始自學vba的初心者，看到這模擬vlookup的程式很想學習。
由於註冊論壇也沒多久，所以沒辦法下載infoverdad大大的附件。
只好看您寫的程式來揣摩，只是礙於我是初心者，其實也根本看不懂你在寫什麼。
後來我只好一句一句的用F1慢慢查，然後再看infoverdad大大貼的圖，用瞎猜的方式做出那一個工作表。
我不確定我做的對不對，至少能發揮跟infoverdad大大說的類似vlookup查詢功能的效果。

以下，想請您幫我看看我做的對不對，我是直接貼上您的程式，然後畫出來的工作表。
[attach]18198[/attach]

大概就是圖中的工作表貼上您的程式可以正常運作。

但重點是我大概知道程式中每一列的作用，但我不懂每一句實際上的用法。
所以想請教您，不曉得您能不能用白話一點的方式為我解釋一下，謝謝~~

Private Sub Worksheet_Change(ByVal Target As Range)  '這一句是不是當target的儲存格有變動的時候就讓sub內的程式執行? 只是為什麼這個sub必須加上private?

Dim wsLists As Worksheet, Rng As Range 'dim我翻書知道大概是宣告的意思吧，所以整句的意思是宣告wsLists是工作表，rng是範圍?

If Target.Count > 1 Then Exit Sub '這句就是我遇到第一個不會的了，if的部分沒什麼問題，target跟Exit Sub我也曉得，只是Count是什麼意思啊?我搞不清楚Count，用F1的說明我實在也看不懂@@

Set wsLists = Worksheets("Lists") '這個是把Lists這個sheet指定給wsLists

Application.EnableEvents = False '這句我google了好久，半知半解，是不是指false的時候先停止這段程式的運作，等被Application.EnableEvents = False跟true包起來的這段程式確實跑完，Application.EnableEvents 變成true，程式才會確實執行?

If Target.Column = 2 Then '這是指target的行數=2就then

      Set Rng = wsLists.Range("A:A").Find(Target, LookAt:=xlWhole, MatchCase:=True) '這個是用find開始查詢了，指定給rng，只是我不懂lookat:=xlWhole是什麼意思，F1的說明也沒有特別說明xlWhole是在幹嘛，MatchCase:=True是把大小寫也列入判斷條件裡吧?

      With Target.Offset(0, 1) '這邊是讓target的結果往右飄一格，只是我不懂為什麼要用with

         If Not Rng Is Nothing Then .Value = Rng.Offset(0, 1).Value Else .Value = ""  'Not Rng Is Nothing，我想了很久，所以應該是負負得正的意思?

下面的東西就是再重複一次只是方向不同對吧?

      End With
ElseIf Target.Column = 3 Then
      Set Rng = wsLists.Range("B:B").Find(Target, LookAt:=xlWhole, MatchCase:=True)
      With Target.Offset(0, -1)
         If Not Rng Is Nothing Then .Value = Rng.Offset(0, -1).Value Else .Value = ""
      End With
End If
Application.EnableEvents = True
End Sub

以上，我問的方式很無腦，因為我對VBA實在是沒什麼基礎可言。
不過我是真的很想學，如果GBKEE版大不嫌麻煩的話，還請您幫我解惑一下。
請盡可能白話一點，F1的說明有的部份我看得很模糊.......
謝謝~~(跪)

作者: yen956 時間: 2014-5-2 10:03

回復 2# GBKEE
每次研究大大的VBA均有很大的收獲, 謝謝!!
但這句的作用是什麼, 還是不了解, 能不能進一步說明, 謝謝!!

If Target.Count > 1 Then Exit Sub

複製代碼

作者: GBKEE 時間: 2014-5-3 16:16

回復 17# yen956
執行Ex看看

Option Explicit
Sub Ex()
ActiveSheet.[A1:A10] = ""
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'Target 的型態是 Range (它是一個儲存格或儲存格範圍)
MsgBox Target.Cells.Count
End Sub

複製代碼

作者: yen956 時間: 2014-5-3 18:41

回復 18# GBKEE
謝謝版大的指導!!
一直以為 Worksheet_Change 是指使用者操作引發的,
沒想到VBA也可引發Worksheet_Change,
且可一次可引發一個大範圍, 謝謝版大的指導!!

作者: yen956 時間: 2014-5-3 18:59

回復 18# GBKEE
我又錯了,
使用者操作也可一次引發一大範圍,
謝謝!!

作者: dryadf 時間: 2014-5-4 01:47

回復 18# GBKEE

感謝GBKEE版大的分享
所以Count就是計算儲存格的數量
可是這樣小弟又多了幾個疑問，不曉得GBKEE版大是否願意解惑

1. 在您find方法內是寫if Target.Count > 1 Then Exit sub
請問是在什麼情況之下Count才會小於1呢? 這樣寫是為了避免什麼情況發生呢?

2. 在執行過您上面分享的Ex之後，我試著亂改裡面的程式看有什麼效果。
然後我改成
Private Sub Worksheet_Change(ByVal Target As Range)

   if  Target.Count < 1 Then

      MsgBox Target.Count

   End If

End Sub
我主要是想試試看修改成您在find方法裡面的用法會有什麼效果
結果我發現<1的時候即便我選擇兩個以上的儲存格也不會執行MsgBox
除非是寫成=1，這樣選擇一個儲存格的時候可以正常的執行MsgBox
如果寫成<=1或>=1的時候，也只有在選擇一個儲存格的時候才能正常執行MsgBox
請問這是為什麼呢?
寫成<1的時候不是該代表我選擇一個以上的儲存格時，就能顯示正常的結果嗎?

3. 您在Ex這段程式中用到了Option Explicit
我用F1說明查到的是說Option Explicit是用來強制每個變數都一定要宣告
為什麼您在Ex中要特別加入這一段呢?
我試著把Option Explicit刪除，也是能夠正常運作，那加入這一段的用意是什麼呢?

小弟魯鈍，希望GBKEE版大能不吝賜教~~~~~謝謝~~

作者: GBKEE 時間: 2014-5-4 15:15

回復 21# dryadf
1. 在您find方法內是寫if Target.Count > 1 Then Exit sub
A: 沒有Target.Count會小於1的, 為避免如下類似如下情況發生.試著 Find方法內去掉這段看看 if Target.Count > 1 Then Exit sub

Option Explicit
Sub Ex()
ActiveSheet.[A1:A10] = ""
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim E As Range
For Each E In Target
MsgBox E.Address
Next
End Sub

複製代碼

2. 在執行過您上面分享的Ex之後，我試著亂改裡面的程式看有什麼效果。
寫成<1的時候不是該代表我選擇一個以上的儲存格時，就能顯示正常的結果嗎?q
A:是>=1吧,代表我選擇一個以上的儲存格.

3. 您在Ex這段程式中用到了Option Explicit
如A 1 中沒Dim E As Range 系統會通知你要宣告變數.

作者: dryadf 時間: 2014-5-5 01:08

回復 22# GBKEE

感謝GKBEE版大的解答~~
忽然覺得自己好蠢，原來我問問題的方向錯了，因為我完全看錯了。
大概是看太久看到眼都花了，我一直以為您是寫 If Target.Count < 1 @@
所以我才會一直以為是為了避免出現Count小於1的狀況發生，原來根本不可能會有Count<1的情況....
真的是很抱歉~~:P

我之前有試過在find方法中把 If Target.Count > 1 Then Exit Sub拿掉測試，可是偵錯的功能沒有阻止我，整個程式還是執行的很開心，功能也都正常。
所以我才搞不懂那一段到底是什麼意思~~~
反倒是把 Application.EnableEvents = False跟 Application.EnableEvents = true拿掉，整個就開始暴走
我當下手足無措，還好按下Esc就停止了

這樣我就明白了，Count > 1是指至少選擇一個以上的儲存格
如果是這樣的話，您find方法內寫的是 If Target.Count > 1 Then Exit Sub
可是Exit Sub不是指離開這個事件嗎? 這樣整句解釋下來不就是等於選擇至少一個以上的儲存格就離開這個事件?
還是我理解錯誤呢?
想再求解一下~~
以上，再次感謝GBKEE版大肯費心為我解惑~~

作者: GBKEE 時間: 2014-5-5 14:55

可是Exit Sub不是指離開這個事件嗎? 這樣整句解釋下來不就是等於選擇至少一個以上的儲存格就離開這個事件?
dryadf 發表於 2014/5/5 01:08

沒錯的

Private Sub Worksheet_Change(ByVal Target As Range)
'ByVal 選擇性引數。表示以傳值的方式來傳遞引數。
'Target : 變數名稱
'As Range : 型態為 Range物件
' Range 物件，該物件代表一個儲存格或儲存格範圍

複製代碼

作者: melvinhsu 時間: 2014-5-9 12:33

請問各位高手，在target 的用法，是如何使用

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