Private Sub CommandButton2_Click()
Dim str As String
Dim b, d0, d1 As Range
Dim s, t, s1, t1, h1, h0 As Long
Dim v, tnt As Long
Dim i, j, k, l, m, n As Integer
Dim wr As Range
If TextBox6.Text = "" Then
MsgBox "請輸入需要查找的物料型號！"
Else
word1 = "物料代碼"
Set d0 = xls3.Range("a2:t65536").Find(word1, lookat:=xlWhole) ‘表xls3為已經打開的表，作為全域變數存在
If Not d0 Is Nothing Then
h0 = d0.Column
MM = ChgNumToABC(h0)
End If
word2 = "MANUFACTURE P/N"
Set d1 = xls3.Range("a2:t65536").Find(word2, lookat:=xlWhole)
If Not d1 Is Nothing Then
h1 = d1.Column
h2 = d1.Row
End If
str = TextBox6.Text
Set b = xls3.Range(Cells(2, h1), Cells(65536, h1)).Find(str, lookat:=xlPart)’這句話這樣寫可以嘛，始終報錯，因為列為變數，需要制定區域范圍
If Not b Is Nothing Then
v = b.Row
s = xls3.Cells(v, h1).Value
t = xls3.Cells(v, h0).Value
ListBox1.AddItem (t)
ListBox2.AddItem (s)
Do
Set b = xls3.Range(Cells(2, h1), Cells(65536, h1)).FindNext(b) ‘ 同樣報錯
tnt = b.Row
s1 = xls3.Cells(tnt, h1).Value
t1 = xls3.Cells(tnt, h0).Value
ListBox1.AddItem (t1)
ListBox2.AddItem (s1)
Loop Until b Is Nothing Or b.Row = v
i = 0
Do While i < ListBox1.ListCount - 1
j = i + 1
Do While j <= ListBox1.ListCount - 1
If ListBox1.List(i) = ListBox1.List(j) Then
ListBox1.RemoveItem j
Else
j = j + 1
End If
Loop
i = i + 1
Loop
k = 0
Do While k < ListBox2.ListCount - 1
l = k + 1
Do While l <= ListBox2.ListCount - 1
If ListBox2.List(k) = ListBox2.List(l) Then
ListBox2.RemoveItem l
Else
l = l + 1
End If
Loop
k = k + 1
Loop
End If
MsgBox "ok!"
End If
End Sub
uj5u.com熱心網友回復：
Set b = xls3.Range(Replace("A2:A65536", "A", ChgNumToABC(h1))).Find(str, lookat:=xlPart)
uj5u.com熱心網友回復：
cells前面加前綴 xls3，Set b = xls3.Range(xls3.Cells(2, h1), xls3.Cells(65536, h1)).Find(str, lookat:=xlPart)
不過，還可以簡寫的......
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/63917.html
標籤：VBA
上一篇：gridview 問題