作者:乔山办公网日期:
返回目录:excel表格制作
'模糊查询e69da5e6ba90e79fa5e98193361代码
Dim i As Long
Dim j As Long
Me.品名查询.MultiSelect = fmMultiSelectMulti
For i = 0 To Me.品名查询.ListCount - 1
Me.品名查询.Selected(i) = False
For j = 0 To Me.品名查询.ColumnCount - 1
If Me.品名查询.Column(j, i) Like "*" & Me.TextBox1.Text & "*" Then
Me.品名查询.Selected(i) = True
Exit For
End If
Next
Next
'提取的代码 提取出的内容会放在一个新工作表中
Dim i As Long
Dim j As Long
Dim k As Long
Dim sht As Worksheet
For i = 0 To Me.品名查询.ListCount - 1
If Me.品名查询.Selected(i) Then j = j + 1
Next
If j = 0 Then MsgBox "没有选中行": Exit Sub
Set sht = ThisWorkbook.Worksheets.Add
k = 1
For i = 0 To Me.品名查询.ListCount - 1
For j = 0 To Me.品名查询.ColumnCount - 1
If Me.品名查询.Selected(i) Then sht.Cells(k, j + 1) = Me.品名查询.Column(j, i)
Next
k = k + 1
Next
答:条件假设:
文本框:TextBox1;列表框:ListBox1;数据区域左上角为A1;此工作表为活动工作表。
添加TextBox1事件代e68a84e8a2ade79fa5e98193361码:
Private Sub TextBox1_Change()
Dim Arr() As Variant
Dim LastRow As Long, Count As Long, i As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
ReDim Arr(1 To 5, 1 To LastRow)
Count = 1
For i = 2 To LastRow
If Cells(i, "B") = TextBox1.Text Then
Arr(1, Count) = Cells(i, "A")
Arr(2, Count) = Cells(i, "B")
Arr(3, Count) = Cells(i, "C")
Arr(4, Count) = Cells(i, "D")
Arr(5, Count) = Cells(i, "E")
Count = Count + 1
End If
Next
ReDim Preserve Arr(1 To 5, 1 To Count)
ListBox1.ColumnCount = 5
ListBox1.List = Application.Transpose(Arr)
End Sub
打开VBA编辑器, 插入百一个度用户窗体, 在窗体中放一个textbox, 一个commandbutton, 然后打开窗体代问码窗口粘贴以下代码
Private Sub CommandButton1_Click()
'读入一个ANSI编码的文答本文件回,并显示在textbox中
On Error GoTo errhand
ipath = ThisWorkbook.Path & "\test.txt"
Open ipath For Input As #1
TextBox1.MultiLine = True
TextBox1.Value = StrConv(InputB(LOF(1), 1), vbUnicode)
Close #1
Exit Sub
errhand:
If Err.Number = 53 Then
MsgBox "当前工作薄目录下未答找到test.txt"
Else
MsgBox "未知错误!"
End If
End Sub
搜索内容很简单,你是想以什么样的形式返回?
搜索的知话遍历行就可以,参考下面的代码
listbox1.Clear
listbox1.AddItem("库位 在库道数量")
For j = 1 To 500 Step 1
If Trim(thisWs.Cells(i, 1)) = Trim(textbox1) and instr(1,thisWs.Cells(i, 2),textbox2) >0 Then
'thisWs.Cells(i, 2).Interior.ColorIndex = 3 '单元格颜色改成红色
listbox1.AddItem(thisWs.Cells(i, 3)&" "&thisWs.Cells(i, 4))'将符合条件的数据加入列内表
'Exit For 允许多条结果时不要这个
End If
Next j
这段代码比较了第一列和textbox1相等,且第二列包含textbox2,变量名自己根据实际情况修改,遍历的容行数也可以自己调整。