我正在尝试使用VBA做一个搜索工具,以在用户输入的给定SearchString的所有表中查找所有出现的事件。应该将在不同工作表中找到此SearchString的整个行粘贴到名为“搜索”的工作表的相应列中。如下图所示:
因此,应该在表格Free REQ中找到的行开始粘贴到单元格A11中,并粘贴到连续的行中,直到在表格Free REQ中找不到更多的对应关系为止,然后应该在表格Temporary中找到的行开始粘贴到单元格A11中。 N11,以此类推,适用于所有工作表(不同工作表中的表与“搜索”工作表中的相应表具有相同的列数)。为此,我有以下代码(TableColumn是“搜索”表中每个表开始的列,例如,对于免费REQ,它将为“ B”):
Private Sub OKButton_Click()
Dim TableColumn As String
Dim SearchString As String
Dim SheetNames() As Variant
Dim Loc As Range
Dim CurrentRow
SearchString = TextBox.Value
'Sheets where we want to find stuff
SheetNames = Array("Free REQs", "Temporary", "FTE", "Hierarchy", "all REQs", "EMEA TEAM")
'Code to find all occurrences in all sheets MAKE SEPARATE SUB FOR THIS (from: http://stackoverflow.com/questions/19504858/find-all-matches-in-workbook-using-excel-vba)
'Start filling the results table in row 11
CurrentRow = 11
For Each Sh In SheetNames
TableColumn = GetTableBeginColumn(Sh)
With Sheets(Sh)
'Find in all sheets where SearchString ocurrs
Set Loc = .UsedRange.Cells.Find(What:=SearchString)
If Not Loc Is Nothing Then
Do Until Loc Is Nothing
'Copy the data from the original source
.Rows(Loc.Row).EntireRow.Copy
'Paste it into the Search sheet
ActiveSheet.Paste Destination:=Sheets("Search").Range(TableColumn & CurrentRow)
'Find the next occurrence of SearchString
Set Loc = .UsedRange.FindNext(Loc)
'Fill the next empty row next
CurrentRow = CurrentRow + 1
Loop
End If
End With
CurrentRow = 11
Set Loc = Nothing
Next
End Sub
虽然,对于这一行:
ActiveSheet.Paste Destination:=Sheets("Search").Range(TableColumn & CurrentRow)
我收到错误消息:
即使TableColumn & CurrentRow
对应于定义单个单元格的字符串。我在这里想念什么?
试试这个小重构
Option Explicit
Private Sub OKButton_Click()
Dim TableColumn As String
Dim SearchString As String
Dim SheetNames() As Variant
Dim Loc As Range
Dim CurrentRow
Dim sh As Variant
Dim firstAddress As String
SearchString = TextBox.Value
'Sheets where we want to find stuff
SheetNames = Array("Free REQs", "Temporary", "FTE", "Hierarchy", "all REQs", "EMEA TEAM")
'Code to find all occurrences in all sheets MAKE SEPARATE SUB FOR THIS (from: http://stackoverflow.com/questions/19504858/find-all-matches-in-workbook-using-excel-vba)
'Start filling the results table in row 11
CurrentRow = 11
For Each sh In SheetNames
TableColumn = GetTableBeginColumn(sh)
With Sheets(sh)
'Find in all sheets where SearchString ocurrs
Set Loc = .UsedRange.Cells.Find(What:=SearchString)
If Not Loc Is Nothing Then
firstAddress = Loc.Address '<--| store first found cell address
Do
'Copy the data from the original source
Intersect(.Rows(Loc.Row).EntireRow, .UsedRange).Copy Destination:=Sheets("Search").Range(TableColumn & CurrentRow) '<--| copy only a "finite" amount of columns
'Find the next occurrence of SearchString
Set Loc = .UsedRange.FindNext(Loc)
'Fill the next empty row next
CurrentRow = CurrentRow + 1
Loop While Loc.Address <> firstAddress '<--| loop until 'Find()' wraps back to first found cell
End If
End With
CurrentRow = 11
Set Loc = Nothing
Next
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句