GBKEE

回復 3# owen06

1. Dim Rng As Range, Ar()
   
2. Private Sub UserForm_Initialize()
   
3.     Ar = Array(TextBox2, TextBox6, TextBox5, TextBox3, TextBox4) '控制項置於陣列
   
4.     With Sheet1
   
5.         Set Rng = .[A3]
   
6.         ComboBox1.RowSource = .Range("a3:a" & [a65536].End(3).Row).Address
   
7.         ComboBox2.RowSource = .[iv65533:iv65536].Address
   
8.     End With
   
9. End Sub
   
10. Private Sub ComboBox1_Change()
    
11.     Dim sel(), i As Integer
    
12.     sel = Array(2, 4, 9, 12, 13)
    
13.     With ComboBox1  '.ListIndex = -1 不在清單中
    
14.         For i = 0 To UBound(Ar)
    
15.             Ar(i).Text = IIf(.ListIndex > -1, Rng.Offset(.ListIndex, sel(i) - 1), "")
    
16.         Next
    
17.     End With
    
18.     'TextBox2 = Application.VLookup(sel, Sheet1.[a3:s65536], 2, False)
    
19.     'TextBox6 = Application.VLookup(sel, Sheet1.[a3:s65536], 4, False)
    
20.     'TextBox5 = Application.VLookup(sel, Sheet1.[a3:s65536], 9, False)
    
21.     'TextBox3 = Application.VLookup(sel, Sheet1.[a3:s65536], 12, False)
    
22.     'TextBox4 = Application.VLookup(sel, Sheet1.[a3:s65536], 13, False)
    
23. End Sub

複製代碼

GBKEE

回復 5# owen06

1. If IsError([D1]) Then
   
2.     '或是 If [D1].Text = "#N/A" Then
   
3.    
   
4.     [D1] = "=IF(ISERROR(VLOOKUP(C1,A1:B3,2,FALSE)),"""",VLOOKUP(C1,A1:B3,2,FALSE))"

複製代碼