根据搜索条件移动特定列
Sub Test3()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error GoTo Err_Execute
'Start search in row 5
LSearchRow = 5
'Start copying data to row 2 in Sheet3 (row counter variable)
LCopyToRow = 2
While Len(Range("Y" & CStr(LSearchRow)).Value) > 0
'If value in column Y = "84312570", copy entire row to Sheet3
If Range("Y" & CStr(LSearchRow)).Value = "84312570" Then
'Select row in MasterList to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy
'Paste row into Sheet3 in next row
Sheets("Sheet3").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste
'Move counter to next row
LCopyToRow = LCopyToRow + 1
'Go back to MasterList to continue searching
Sheets("MasterList").Select
End If
LSearchRow = LSearchRow + 1
Wend
'Position on cell A5
Application.CutCopyMode = False
Range("A5").Select
MsgBox "All matching data has been copied."
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub
这会在 Y 列中查找特定值,并将相应信息的整行移动到各个工作表中。
我有两个问题。
首先,有没有办法指定仅将某些列的信息移动到各个工作表而不是移动整行?
其次,有没有办法仅根据 Y 列中数字序列的最后 4 位提取信息?例如,上面我想要提取 Y 列中的数字与 *2570 匹配的所有行。
Sub Test3()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error GoTo Err_Execute
'Start search in row 5
LSearchRow = 5
'Start copying data to row 2 in Sheet3 (row counter variable)
LCopyToRow = 2
While Len(Range("Y" & CStr(LSearchRow)).Value) > 0
'If value in column Y = "84312570", copy entire row to Sheet3
If Range("Y" & CStr(LSearchRow)).Value = "84312570" Then
'Select row in MasterList to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy
'Paste row into Sheet3 in next row
Sheets("Sheet3").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste
'Move counter to next row
LCopyToRow = LCopyToRow + 1
'Go back to MasterList to continue searching
Sheets("MasterList").Select
End If
LSearchRow = LSearchRow + 1
Wend
'Position on cell A5
Application.CutCopyMode = False
Range("A5").Select
MsgBox "All matching data has been copied."
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub
This finds specific values in Column Y and moves entire rows of corresponding information to individual worksheets.
I have two questions.
First, is there a way to specify only certain columns of information be moved to the individual sheets instead of moving the entire row?
Second, is there a way to pull information based off of only the last 4 digits of the number sequence in column Y? For example, above I would want to pull all rows whose number in column Y matched *2570.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
未经测试:编辑 arrColsToCopy 以包含要复制的列
Untested: edit arrColsToCopy to include the columns you want copied over
是的。您可以使用循环将列收集到 Union不连续范围对象或使用 交叉方法应用于所需列的预制范围。相交还可以应用于 xlCellTypeVisible 行应用 Range.AutoFilter 方法。
构建 Scripting.Dictionary 对象使用模式匹配来匹配键值,并使用字典的键作为 自动筛选,运算符参数为 < a href="https://msdn.microsoft.com/en-us/library/office/ff839625.aspx" rel="nofollow noreferrer">xlFilterValues。 Select Case 语句提供了简单的模式匹配方法。
循环填充、过滤和传输的过程可以轻松地循环遍历数组中的关联值。
源数据
目的地结果
Yes. You can use a loop to collect the columns into a Union of discontiguous Range objects or sweep them with an Intersect method applied against a preformed range of the desired columns. The Intersect can also be applied against the xlCellTypeVisible rows from an applied Range.AutoFilter method.
Build a Scripting.Dictionary object of the matching key values using a pattern match and use the dictionary's keys as the array of criteria of AutoFilter with an Operator parameter of xlFilterValues. A Select Case statement provides easy pattern matching methods.
Cycling the process of populating, filtering and transferring could easily be looped through associated values in an array.
Source data
Destination results