帮同学写了个查询用的,还有很多问题。。第一版先贴出来。
这是生成新sheet来存放查找信息的
'初始化userform
Private Sub FindIt_Initialize()
End Sub
Private Sub OkButton_Click()
Dim str As String
Dim i, j, d, coni, findCount As Integer
Dim findRows() As Integer
Dim oldSheetName, newSheetName As String
str = TextBox1.Value '获取用户填入的货号
findCount = 0
unMer '调用自定义函数,合并单元格
ReDim findRows(findCount)
coni = Asc("B") - 64 '查找B列
j = Range("e65536").End(xlUp).Row '返回e列最后一个非空单元格的行号
For i = 1 To j '查找从第一行到最后一行
If Cells(i, coni) = str Then '如果是自己想要的值
findCount = findCount + 1
ReDim Preserve findRows(findCount)
findRows(findCount - 1) = i
'MsgBox ("get it (" & findRows(findCount - 1) & ")!")
Else
' MsgBox ("not get (" & i & ")!")
End If
Next i
MsgBox (UBound(findRows))
oldSheetName = ActiveSheet.Name
newSheetName = "findByLee"
Sheets.Add.Name = newSheetName
For d = 0 To UBound(findRows) - 1
Sheets(oldSheetName).Select
Rows(findRows(d)).Select
Selection.Copy
Sheets(newSheetName).Select
Rows(d + 1).Select
ActiveSheet.Paste
Next d
End Sub
Function unMer()
'
' Macro1 Macro
' 宏由 julian.zhang 录制,时间: 2010-2-4
'
'
Dim rg As Range
For zyd_c = 1 To ActiveSheet.UsedRange.Columns.Count
For i = 1 To ActiveSheet.UsedRange.Rows.Count
Cells(i, zyd_c).Select
If Selection.MergeCells = True Then
Selection.UnMerge
For Each rg In Selection
rg.Value = Cells(i, zyd_c)
Next rg
End If
Next i
Next zyd_c
Cells(1, 1).Activate
End Function
效果如下:
新生成一个sheet来存放数据
这里是直接隐藏不拼配的信息
'初始化userform
Private Sub FindIt_Initialize()
End Sub
Private Sub OkButton_Click()
Dim str As String
Dim i, j, coni As Integer
str = TextBox1.Value '获取用户填入的货号
unMer '调用自定义函数,合并单元格
coni = Asc("B") - 64 '查找B列
j = Range("e65536").End(xlUp).Row '返回e列最后一个非空单元格的行号
For i = 1 To j '查找从第一行到最后一行
If Cells(i, coni) = str Then '如果是自己想要的值
Else
Rows(i).Hidden = True
End If
Next i
End Sub
Function unMer()
'
' Macro1 Macro
' 宏由 julian.zhang 录制,时间: 2010-2-4
'
'
Dim rg As Range
For zyd_c = 1 To ActiveSheet.UsedRange.Columns.Count
For i = 1 To ActiveSheet.UsedRange.Rows.Count
Cells(i, zyd_c).Select
If Selection.MergeCells = True Then
Selection.UnMerge
For Each rg In Selection
rg.Value = Cells(i, zyd_c)
Next rg
End If
Next i
Next zyd_c
Cells(1, 1).Activate
End Function
Private Sub ShowAll_Click()
Dim i, j As Integer
j = Range("e65536").End(xlUp).Row '返回e列最后一个非空单元格的行号
For i = 1 To j '查找从第一行到最后一行
Rows(i).Hidden = False
Next i
End Sub
总之 结果如下:
未处理:
处理中:
处理后:
可以通过点击显示全部来展示隐藏掉的部分。