|
做了个窗体,帖代码如下。
按“搜寻资料”窗体自动运行。
- Dim brr()
- Private Sub CommandButton1_Click()
- crr = Me.ListBox1.List
- For i = 0 To Me.ListBox1.ListCount - 1
- If Me.ListBox1.Selected(i) = True Then xstr = xstr & "," & Me.ListBox1.List(i)
- Next
- xstr = Mid(xstr, 2)
- If Len(xstr) Then Call Sel(xstr) Else MsgBox "Please Selcet At Least One LotNo"
- Unload Me
- End Sub
- Private Sub CommandButton2_Click()
- Unload Me
- End Sub
- Private Sub TextBox1_Change() '文本框改变引发下拉框列表改变
- Dim str$, i%, j%, k%
- str = UCase(Trim(TextBox1))
- If Len(str) = 0 Then
- ListBox1.List = brr
- Exit Sub
- Else
- ListBox1.Clear
- For j = 1 To UBound(brr)
- kh = brr(j)
- If Len(kh) > 0 Then
- If kh Like "*" & str & "*" Then
- i = i + 1
- ListBox1.AddItem brr(j) ' AddItem 对于单列的列表框或组合框,在列表中添加一项。对于多列的列表框或组合框,在列表中添加一行。
- End If
- End If
- Next
- End If
- End Sub
- '
- Private Sub UserForm_Initialize()
- Set d = CreateObject("scripting.dictionary")
- With Sheets(1)
- arr = .Range("a6:a" & .[a65536].End(3).Row)
- For i = 1 To UBound(arr)
- If Len(arr(i, 1)) Then d(arr(i, 1)) = ""
- Next
- brr = d.keys
- Me.ListBox1.List = brr
- End With
- End Sub
- Private Sub Sel(xstr)
- Dim i, j, jj, k, x, LotNo
- Dim ToRange As Range
- Dim tmpArr(), n(), arr
- With Worksheets(1) '读入数据源
- arr = .Range("a1:av" & .[a65536].End(3).Row)
- End With
- With ActiveSheet
- Set ToRange = .Range("F4:J18")
- ReDim tmpArr(1 To ToRange.Rows.Count, 1 To 5)
- ReDim n(1 To ToRange.Rows.Count) '数组n记录每组数的记录位置
- ToRange.ClearContents: .[I2] = ""
- xrr = Split(xstr, ",")
- For i = 6 To UBound(arr)
- LotNo = arr(i, 1) '批号
- For Each x In xrr
- If LotNo = x Then
- .Range("I2") = LotNo 'I2显示为批号(这样vlookup结果就正确了,但当符合条件的有多条记录时,会只显示最后一条记录的批号)
- For j = 5 To 47 Step 3 'E列到AU列
- k = (j - 2) / 3 '根据源数据的列转换到tmparr的行
- For jj = 0 To 2 '每列共3列需录入tmpArr(最后一次2列)
- If j + jj <= UBound(arr, 2) Then '限定边界(最后一次只计算AU--AV列)
- If arr(i, j + jj) <> "" Then '如果数据源需录入的列非空
- n(k) = n(k) + 1 'tmpArr对应行每组的记录位置+1
- If n(k) <= 5 Then tmpArr(k, n(k)) = arr(i, j + jj) '源数据保存到记录位置(最多只保存5个)
- End If
- End If
- Next jj
- Next j
- End If
- Next
- Next i
- ToRange = tmpArr '显示结果
- End With
- End Sub
复制代码 |
|