vba 如何指定特定行和特定列的范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18194954/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to specify a range that is a specific row and specific columns?
提问by user2676066
I search Column A for a specific value. I want copy the row, that contains the value, from columns C to O, to another sheet.
我在 A 列中搜索特定值。我想将包含值的行从 C 列复制到 O 列到另一个工作表。
The worksheet labeled Data is where I am pulling information from columns C to O.
标有数据的工作表是我从 C 列到 O 列提取信息的地方。
The worksheet labeled Sales is where I want the data pasted.
标记为 Sales 的工作表是我想要粘贴数据的位置。
Sub Test_Copy_Data()
'Macro to copy sales based on site
'**********************************************************
Dim numentries As Integer
Dim i As Integer
Dim site As String
'***********************************************************
'Define sheet names
Data = "Data1"
Sales = "Sales"
'************************************************************
'Get number of entries and desired scores
numentries = Worksheets(Data).UsedRange.Rows.Count
'*************************************************************
site = Worksheets(Sales).Range("B1").Value
'************************************************************
'Run loop to cycle through all entries (rows) to copy
For i = 1 To numentries
If (Worksheets(Data).Cells(i + 2, 1).Value = site) Then
Worksheets(Data).Range("C:O" & i + 2).Copy _
Destination:=Worksheets(Sales).Range("A2")
End If
Next i
End Sub
回答by Jaycal
Your reference to the range on your Data1 worksheet is incorrect. Update
您对 Data1 工作表上的范围的引用不正确。更新
Worksheets(Data).Range("C:O" & i + 2).Copy Destination:=_
Worksheets(Sales).Range("A2")
to...
到...
Worksheets(Data).Range("C" & i + 2 & ":O" & i + 2).Copy Destination:=_
Worksheets(Sales).Range("A2")
and it should work. Note, however, that every time this codes runs, it will paste the found value from the Data sheet into the same location on the Sales sheet (cell A2), so just a guess, but you may want to update this reference as well.
它应该工作。但是请注意，每次运行此代码时，它都会将数据表中找到的值粘贴到销售表（单元格 A2）上的相同位置，因此只是猜测，但您可能还想更新此引用。
(e.g. Worksheets(Sales).Range("A" & i + 1))
（例如Worksheets(Sales).Range("A" & i + 1)）
