I am trying to make a macro that selects certain data in my sheet. I have a sheet with data that is pulled into it using:
我正在尝试做一个宏来选择我的表中的某些数据。我有一张包含数据的表格，它使用:
Windows("Item checkout workbook_New.xlsx").Activate
Range("A2:G300").Select
Selection.Copy
Windows("VLookup test.xlsx").Activate
Sheets("Sheet1").Select
Range("A2:G2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Sheet1").Range("A2:G300").Copy Sheets("Sheet2").Range("A2")
Sheets("Sheet2").Select
Application.CutCopyMode = False
Once this data is input, I have two columns H2:H300 and I2:I300 that has formulas already in it for Vlookup that get information from A2:G300.
一旦输入了这个数据，我就有了两个列H2:H300和I2:I300，其中包含了用于从A2:G300获取信息的Vlookup的公式。
What I then need to do is select only the relevant data and copy it back to Windows("Item checkout workbook_New.xlsx"). By relevant data, I need to select only cells with data in the A2:G300 range as well as the H2:I300 cells that match. Seeing as ALL H2:I300 cells have data, I am not sure how to do this. I tried to create a macro that uses END to select all of column A and then the rows that go with it, but this is what I got and as you can see it will not work:
然后我需要做的是只选择相关的数据并将其复制回Windows(“Item checkout workbook_New.xlsx”)。通过相关数据，我只需要选择具有A2:G300范围内的数据的单元格，以及匹配的H2:I300单元格。由于所有的H2:I300细胞都有数据，所以我不确定如何做到这一点。我尝试创建一个宏，使用END选择a列的所有行，然后选择与之匹配的行，但这就是我得到的结果，正如你所看到的，它不能工作:
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:I78").Select
Selection.Copy
I am not great at VBA thus it is hard to come up with things on the fly, but I feel like there should be a way to get this to work. Any advice would be great!
我在VBA不是很擅长，所以很难马上想出什么东西，但我觉得应该有办法让它发挥作用。任何建议都是好的!
2 个解决方案
#1
1
Range("A2").Select
Range(Selection, Selection.End(xlDown)).EntireRow.Select
Selection.Copy
Windows("Item checkout workbook_New.xlsx").Activate
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Got it working!
找到了工作!
#2
0
Based off your own answer, it seems I may have misinterpreted your question. What I gathered was that you were looking for a way to select relevant cells in a worksheet. The relevant cells could belong to one of two ranges. In one range, cells that are not empty should be selected. In the other range, cells that match a value should be selected. (you can just add in your copy/paste code)
根据你自己的回答，我似乎误解了你的问题。我收集到的是，您正在寻找一种方法来选择工作表中的相关单元格。相关的细胞可以属于两个范围中的一个。在一个范围内，应该选择不为空的单元格。在另一个范围中，应该选择与值匹配的单元格。(只需添加复制/粘贴代码即可)
I solved that problem below.
我解决了下面的问题。
Sub test()
'store results here
Dim result As Range
setupTest
'check this range and return items that are not empty
selectMatchingCells Range("A1:D1"), result
'check this range and return items that match value
selectMatchingCells Range("B2:C4"), result, "hi"
result.Select
End Sub
Function setupTest()
Range("A1").Value = "anything"
Range("c1").Value = "may go"
Range("D1").Value = "here"
Range("B2").Value = "hi"
Range("B3").Value = "but not here"
End Function
Function selectMatchingCells(search As Range, result As Range, Optional searchValue As String = "")
For Each cell In search
'are we checking that cell value matches string, or if cell has a value at all?
If searchValue = vbNullString Then
'check if cell is not empty
If IsEmpty(cell) = False Then selectCell result, cell
Else
'check if value matches
If cell.Text = searchValue Then selectCell result, cell
End If
Next cell
End Function
Function selectCell(result As Range, cell As Variant)
'check if result range already exists or not
If result Is Nothing Then
'make range equal to cell
Set result = cell
Else
'add cell to existing range
Set result = Union(result, cell)
End If
End Function
Please be more clear to avoid miscommunication, thanks!
请讲清楚，避免误会，谢谢!
#1
1
Range("A2").Select
Range(Selection, Selection.End(xlDown)).EntireRow.Select
Selection.Copy
Windows("Item checkout workbook_New.xlsx").Activate
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Got it working!
找到了工作!
#2
0
Based off your own answer, it seems I may have misinterpreted your question. What I gathered was that you were looking for a way to select relevant cells in a worksheet. The relevant cells could belong to one of two ranges. In one range, cells that are not empty should be selected. In the other range, cells that match a value should be selected. (you can just add in your copy/paste code)
根据你自己的回答，我似乎误解了你的问题。我收集到的是，您正在寻找一种方法来选择工作表中的相关单元格。相关的细胞可以属于两个范围中的一个。在一个范围内，应该选择不为空的单元格。在另一个范围中，应该选择与值匹配的单元格。(只需添加复制/粘贴代码即可)
I solved that problem below.
我解决了下面的问题。
Sub test()
'store results here
Dim result As Range
setupTest
'check this range and return items that are not empty
selectMatchingCells Range("A1:D1"), result
'check this range and return items that match value
selectMatchingCells Range("B2:C4"), result, "hi"
result.Select
End Sub
Function setupTest()
Range("A1").Value = "anything"
Range("c1").Value = "may go"
Range("D1").Value = "here"
Range("B2").Value = "hi"
Range("B3").Value = "but not here"
End Function
Function selectMatchingCells(search As Range, result As Range, Optional searchValue As String = "")
For Each cell In search
'are we checking that cell value matches string, or if cell has a value at all?
If searchValue = vbNullString Then
'check if cell is not empty
If IsEmpty(cell) = False Then selectCell result, cell
Else
'check if value matches
If cell.Text = searchValue Then selectCell result, cell
End If
Next cell
End Function
Function selectCell(result As Range, cell As Variant)
'check if result range already exists or not
If result Is Nothing Then
'make range equal to cell
Set result = cell
Else
'add cell to existing range
Set result = Union(result, cell)
End If
End Function
Please be more clear to avoid miscommunication, thanks!
请讲清楚，避免误会，谢谢!