[發問] VLOOKUP問題請教

rouber590324

DEAR ALL大大
  請問如下執行後出現錯誤.抓不到資料??
請問何處有問題  煩不吝賜教.  THANKS*10000
*SHEET1 之 A欄對應 SHEET2之A欄後抓取SHEET2之B欄至SHEET1 之 B欄*

Sub AA()
Sheet1.Select
Range("A1").Select
For X = 2 To 4
If 1 = 1 Then
M = Sheet1.Cells(X, 1)
   mymax = Evaluate("VLOOKUP(M,工作表2!A:B,2,FALSE)")
   Sheet1.Cells(X, 2) = mymax
End If
Next

End Sub

diolin

回復 1# rouber590324

改成  Evaluate("VLOOKUP(""" & M &  """,工作表2!A:B,2,FALSE)")

rouber590324

DEAR  diolin  大大
  TEST 後完全符合需求.感謝您之指導 THANKS*10000

minture3

請教各位達人:
小弟想在自訂表單中應用VLOOKUP函數
如下圖所示
請教各位先進程式碼應該如何寫!!?
謝謝!!
[attach]20496[/attach]

rouber590324

DEAR ALL大大
圖二為程式碼
圖一之 Z 要如何改方可執行.煩不吝賜教.  THANKS*10000

圖一
mymax = Evaluate("VLOOKUP(""" & M & """,'結果2'!A:Y,Z,FALSE)")
圖二
Sub 一()
Sheet6.Select
Range("A1").Select
Sheet6.[D4:AA7].ClearContents
  For X = 4 To 7
  For Y = 4 To 7
  For Z = 2 To 5
   If 1 = 1 Then
     M = Sheet6.Cells(X, 3)
     mymax = Evaluate("VLOOKUP(""" & M & """,'結果2'!A:Y,Z,FALSE)")
     If Application.IsError(mymax) = True Then mymax = "" '取代VLLOUP判斷2
     Sheet6.Cells(X, Y) = mymax
   End If
Next
Next
Next
End Sub

GBKEE

回復 1# rouber590324

1. mymax = Application.WorksheetFunction.VLookup(M, Sheets("結果2").[A:Y], Z, False)

複製代碼

rouber590324

DEAR 大大
測試結果  4-7欄 都跑出  Z=5之值
無依 Z=2之值至 4欄
無依 Z=3之值至 5欄
無依 Z=4之值至 6欄
無依 Z=5之值至 7欄
--煩不吝賜教  THANKS*10000
For Z = 2 To 5
For Y = 4 To 7

mymax = Application.WorksheetFunction.VLookup(M, Sheets("結果2").[A:Y], Z, False)

GBKEE

回復 7# rouber590324
結果2'!A:Y 這範圍是這樣嗎?

1. mymax = Application.WorksheetFunction.VLookup(M, Sheets("結果2").[A:A].Resize(, Y), Z, False)

複製代碼

rouber590324

DEAR 大大
1. mymax = Application.WorksheetFunction.VLookup(M, Sheets("結果2").[A:A].Resize(, Y), Z, False)- 秀出    無法取得類別    WorksheetFunction的VLookup屬性.
  1.1 煩不吝賜教.
2.小弟先用如下較覆雜之方式運作.

Sub 一級()
Sheet6.Visible = True
Sheet1.Visible = False
Sheet6.Select
Range("A1").Select
Sheet6.[D4:AC7].ClearContents
  For X = 4 To 7
   If 1 = 1 Then
     M = Sheet6.Cells(X, 3)
     mymax = Evaluate("VLOOKUP(""" & M & """,'結果2'!A:AA,2,FALSE)")
     If Application.IsError(mymax) = True Then mymax = "" '取代VLLOUP判斷2
     Sheet6.Cells(X, 4) = mymax
     
     mymax1 = Evaluate("VLOOKUP(""" & M & """,'結果2'!A:AA,3,FALSE)")
     If Application.IsError(mymax) = True Then mymax = "" '取代VLLOUP判斷2
     Sheet6.Cells(X, 5) = mymax1
     
     mymax2 = Evaluate("VLOOKUP(""" & M & """,'結果2'!A:AA,4,FALSE)")
     If Application.IsError(mymax) = True Then mymax = "" '取代VLLOUP判斷2
     Sheet6.Cells(X, 6) = mymax2
     
     mymax3 = Evaluate("VLOOKUP(""" & M & """,'結果2'!A:AA,5,FALSE)")
     If Application.IsError(mymax) = True Then mymax = "" '取代VLLOUP判斷2
     Sheet6.Cells(X, 7) = mymax3

     
    Range("A1").Select
   End If
Next

End Sub

GBKEE

回復 9# rouber590324

秀出    無法取得類別    WorksheetFunction的VLookup屬性.

這現象討論區有討論過: (http://forum.twbts.com/viewthread.php?tid=10339)
當Application.WorksheetFunction.(工作表函數),傳回錯誤值時,VBA會有錯誤信息.
拿掉WorksheetFunction可修正.

1. mymax = Application.VLookup(M, Sheets("結果2").[A:A].Resize(, Y), Z, False)

複製代碼