Board logo

標題: [發問] 請問關於跨工作表vlookup的vba語法 [打印本頁]
作者: e19821223    時間: 2013-9-16 12:12     標題: 請問關於跨工作表vlookup的vba語法

  1. Private Sub WORKSHEET_CHANGE()
  2. Dim data As Range
  3. Set data = ['sheet2'!$A$1:$B$10]
  4. [b1] = Application.WorksheetFunction.VLookup([a1], data, 2, 0)
  5. End Sub
複製代碼
以上是我爬論壇的文所寫出來的 想要在sheet1的a1:a10打上編號 則b1:b10參造a1:a10的編碼找尋sheet2的資料
但 卻出現了"無法取得類別 worksheetfanction的vlookup屬性"這訊息

煩請各位大大指教一下 該怎麼修正完善 非常感恩!
作者: GBKEE    時間: 2013-9-16 15:29

本帖最後由 GBKEE 於 2013-9-16 15:34 編輯

回復 1# e19821223
  參考這裡 stillfish00 的研究  
  1. Private Sub WORKSHEET_CHANGE()
複製代碼
這程序名稱,是VBA工作表物件模組的內建事件名稱,不要亂用
作者: e19821223    時間: 2013-9-17 08:51

需要花時間研究 還是不是很懂 感謝G大的指教~
作者: oobird    時間: 2013-9-17 09:28

第一行保持事件的完整參數
Private Sub WORKSHEET_CHANGE(ByVal Target As Range)
就可以執行了
作者: e19821223    時間: 2013-9-17 16:14

感謝O大的回覆 我依照O大所打的 改成
Private Sub WORKSHEET_CHANGE(ByVal Target As Range)
Dim data As Range
Set data = ['sheet2'!$A$1B$10]
[b1] = Application.WorksheetFunction.VLookup([a1], data, 2, 0)
End Sub

可行了
可運作了
但出現了警告標語"堆疊空間不足" 不知道是什麼問題呢
還有 我今天 是用A1 B1
但 我若是想要在A1:A10 ,B1:B10都想要呈現 那 要打10次嗎?
我是非常新的新手..尚在學習 先感謝各位路過的高手的指教
作者: e19821223    時間: 2013-9-20 08:54

  1. Private Sub WORKSHEET_CHANGE(ByVal Target As Range)
  2. Dim data As Range
  3. Set data = ['sheet2'!$A$1B$1000]
  4. [b1] = Application.WorksheetFunction.VLookup([a1], data, 2, 0)
  5. End Sub
複製代碼
以上的不能使用...當輸入後電腦變的超級慢 excel整個當掉
不知是否有更好的方式呢 謝謝指教
作者: GBKEE    時間: 2013-9-20 09:20

回復 6# e19821223
VBA 說明
  1. EnableEvents 屬性
  2. 請參閱套用至範例特定如果指定物件能觸發事件，則本屬性為 True。讀/寫 Boolean。
複製代碼

WORKSHEET_CHANGE 當使用者或外部連結變更工作表中的儲存格時發生此事件
[b1] = Application.VLookup([a1], data, 2, 0) ,會一直引發此事件.
   
  1. Option Explicit
  2. Private Sub WORKSHEET_CHANGE(ByVal Target As Range)
  3.     Dim data As Range
  4.     Application.EnableEvents = False
  5.     Set data = ['sheet2'!$A$1:B$1000]
  6.     [b1] = Application.VLookup([a1], data, 2, 0)
  7.     Application.EnableEvents = True
  8. End Sub
複製代碼
作者: e19821223    時間: 2013-9-20 10:38

感謝O大的指教!
終於止住不運轉了!

不過 當執行此語法後 在B1的部分 就不能手動輸入了
有什麼辦法是可以說依然可以手動輸入呢?
再次麻煩了 非常感謝
作者: GBKEE    時間: 2013-9-20 11:07

回復 8# e19821223
  1. If Target.Address(0, 0) = "B1" Then Exit Sub
複製代碼
作者: e19821223    時間: 2013-9-20 11:22

回復 9# GBKEE
再次謝謝G大
不過 當在A輸入值時 B會出現"#N/A" 的情況(因為找不到值)
小弟我很單純的多加一個IF 語法如下:
  1. If [c13:c32] = "#N/A" Then
  2. [c13:c32] = ""
  3.      
  4. End If
複製代碼
恩..我不是很懂的看了我自己寫的也笑了...好像沒那麼單純..
根本無法執行...

再次請教G大了
作者: GBKEE    時間: 2013-9-20 11:43

本帖最後由 GBKEE 於 2013-9-20 11:44 編輯

回復 10# e19821223
是這樣嗎?
  1. Option Explicit
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     Dim data As Range
  4.     Application.EnableEvents = False
  5.     If Target.Address(0, 0) = "B1" Then Exit Sub
  6.     With Range("C13:C32")
  7.         If Not .Find(What:="#N/A", LookIn:=xlValues) Is Nothing Then .Cells = ""
  8.     End With
  9.     Set data = ['sheet2'!$A$1:B$1000]
  10.     [b1] = Application.VLookup([a1], data, 2, 0)
  11.     Application.EnableEvents = True
  12. End Sub
複製代碼
作者: e19821223    時間: 2013-9-20 13:59

回復 11# GBKEE
  1. 非常感謝G大的教導
  2. Option Explicit
  3. Private Sub Worksheet_Change(ByVal Target As Range)
  4.     Dim data As Range
  5.    [color=Red] Dim x As Range
  6.     Dim y As Range[/color]
  7.     Application.EnableEvents = False
  8.     If Target.Address(0, 0) = "[color=Red]B1:B10[/color]" Then Exit Sub
  9.     With Range("[color=Red]B1:B10[/color]")
  10.         If Not .Find(What:="#N/A", LookIn:=xlValues) Is Nothing Then .Cells = ""
  11.     End With
  12.     Set data = ['sheet2'!$A$1:B$1000]
  13.     Y = Application.VLookup(X, data, 2, 0)
  14.     Application.EnableEvents = True
  15. End Sub
複製代碼
測試以上的語法 但 好像還是不行 正在尋思 是否哪裡有衝突
作者: e19821223    時間: 2013-9-20 14:48

回復 11# GBKEE
我將檔案上傳了 請G大有空過目一下 謝謝
作者: GBKEE    時間: 2013-9-20 16:12

回復 13# e19821223
是期望如此嗎?
  1. Option Explicit
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     Dim data As Range, Y As Variant
  4.     Application.EnableEvents = False
  5.     If Not Intersect(Target, [A1:A10]) Is Nothing Then
  6.        ' Set data = ['sheet2'!$A$1:B$25]
  7.         'Y = Application.VLookup(Target, data, 2, 0)
  8.         Y = Application.VLookup(Target, ['sheet2'!$A$1:B$25], 2, 0)
  9.         Target.Offset(, 1) = IIf(IsError(Y), "", Y)
  10.      End If
  11.     Application.EnableEvents = True
  12. End Sub
複製代碼
作者: e19821223    時間: 2013-9-20 16:46

哇! 是的 我就是要呈現如此 感謝G大的幫忙!
現在比較不懂的
If Not Intersect(Target, [A1:A10]) Is Nothing Then
Target.Offset(, 1) = IIf(IsError(Y), "", Y)
以上兩段的意思
不知是否能再煩請G大的解惑
抱歉 我知道我煩了點...
作者: GBKEE    時間: 2013-9-20 17:18

回復 15# e19821223
vba的說明中有範例
  1. Intersect 方法
  2. 請參閱套用至範例特定傳回 Range 物件，此物件代表兩個或多個範圍重疊的矩形範圍。

  4. IIf 函數
  5. 根據某運算式的值，來傳回兩部份中的其中一個。
  6. 語法
  7. IIf(expr, truepart, falsepart)
複製代碼
作者: e19821223    時間: 2013-9-20 17:41

G大 非常謝謝你!
我再多爬一下文 再次感謝您提供的資訊
作者: e19821223    時間: 2013-9-21 09:23

回復 16# GBKEE
G大..你說的"VBA 說明"是在哪裡@@ 爬了好一段時間 依然沒找到
抱歉 能否在指引一下明路 謝謝
作者: GBKEE    時間: 2013-9-21 09:37

回復 18# e19821223

[attach]16119[/attach]
作者: e19821223    時間: 2013-9-21 10:59

回復 19# GBKEE
呃...我是典型的捨近求遠...我一直還在論壇爬文 都沒看到類似的 原來在電腦說明裡

還有 我想問一下 若說 用相同的語法 但 放在不同的SHEET裡 參造的範圍也不一樣 是否可行呢
比方說 原本放在SHEET1裡的是G大所提供的
  1. Option Explicit
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     Dim data As Range, Y As Variant
  4.     Application.EnableEvents = False
  5.     If Not Intersect(Target, [A1:A10]) Is Nothing Then
  6.        ' Set data = ['sheet2'!$A$1:B$25]
  7.         'Y = Application.VLookup(Target, data, 2, 0)
  8.         Y = Application.VLookup(Target, ['sheet2'!$A$1:B$25], 2, 0)
  9.         Target.Offset(, 1) = IIf(IsError(Y), "", Y)
  10.      End If
  11.     Application.EnableEvents = True
  12. End Sub
複製代碼
但 我要再想要在SHEET3(D1:D10) 參造SHEET4 (C1:D25) 就修正成
  1. Option Explicit
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     Dim data As Range, Y As Variant
  4.     Application.EnableEvents = False
  5.     If Not Intersect(Target, [D1:D10]) Is Nothing Then
  6.       
  7.         Y = Application.VLookup(Target, ['sheet4'!$C$1:D$25], 2, 0)
  8.         Target.Offset(, 1) = IIf(IsError(Y), "", Y)
  9.      End If
  10.     Application.EnableEvents = True
  11. End Sub
複製代碼
但 我在測試時 在SHEET3並無動作(我是有將語法放到SHEET3裡了)
位移數也相同 要的條件也相同 只是參造的SHEET不一樣而已 卻無法執行 不知到底是為什麼
作者: GBKEE    時間: 2013-9-21 11:23

回復 20# e19821223
2# 有說: 這程序名稱,是VBA工作表物件模組的內建事件名稱,不要亂用
每一工作表都可以去編設這些內建事件 ,為何不行上傳檔案看看
如圖

    [attach]16120[/attach]
作者: e19821223    時間: 2013-9-21 11:31

抱歉 我再仔細看了一下 是我的工作表名稱打錯 目前OK了 感謝G大!
作者: a31075ha    時間: 2013-11-16 01:48

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim data As Range, Y As Variant
    Application.EnableEvents = False
    If Not Intersect(Target, [A1:A10]) Is Nothing Then
       ' Set data = ['sheet2'!$A$1:B$25]
        'Y = Application.VLookup(Target, data, 2, 0)
        Y = Application.VLookup(Target, ['sheet2'!$A$1:B$25], 2, 0)
        Target.Offset(, 1) = IIf(IsError(Y), "", Y)
     End If
    Application.EnableEvents = True
End Sub


大大想請問這一段是否有辦法把外框字體顏色也一併帶過來呢??? 感恩.......



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