vba 寫vlookup

2011-10-03 11:03 pm

如下圖:
我只會用這方法,但我有數千個lookup時,就需寫數千個..有否vba可簡單點?
因本人只是自學形式..請高人指點!! 謝謝!!!

Sub 按鈕19_Click()

[A1] = [IF(VLOOKUP(B1,C1:N100,3,FALSE))]
[A2] = [IF(VLOOKUP(B1,C1:N100,4,FALSE))]
[A3] = [IF(VLOOKUP(B1,C1:N100,5,FALSE))]
[A4] = [IF(VLOOKUP(B1,C1:N100,6,FALSE))]
[A5] = [IF(VLOOKUP(B1,C1:N100,7,FALSE))]
[A6] = [IF(VLOOKUP(B1,C1:N100,8,FALSE))]
[A7] = [IF(VLOOKUP(B1,C1:N100,9,FALSE))]
[A8] = [IF(VLOOKUP(B1,C1:N100,10,FALSE))]
[A9] = [IF(VLOOKUP(B1,C1:N100,11,FALSE))]
[A10] = [IF(VLOOKUP(B1,C1:N100,12,FALSE))]

End Sub

回答 (1)

Stanley

2011-10-04 12:06 am

✔ 最佳答案

首先就你所寫的內容,本人有小小覺得怪怪地,你想用VOOKUP,又何來要加if ? 既然你想用if,但又沒有判斷的內容,同要求的結果.

點都好.我就你的問題, (你想知vlookup 點寫 vba ) , 我用marco做
即係公式 = "VLOOKUP($B$1,$C$1:$N$100,ROW()+2,FALSE)"

所以 vba 內容為:
Dim i As Integer
Range("A1:A10") = "" '(清除舊有資料)
For i = 1 To 10 '(決定有幾多行)
Cells(i, "A").FormulaR1C1 = "=VLOOKUP(R1C2,R1C3:R100C14,ROW()+2,FALSE)"
Cells(i, "A") = Cells(i, "A").Value
Next i
End Sub

👍 0 👎 0