vba 查找一列中具有相同值的单元格并从同一行的不同列返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18461590/
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
Find cells with same value within one column and return values from separate column of same row
提问by user2530250
I want to find all the cells in Column Lwith a particular value and return the values in Column Dof the same row as those cells found.
我想找到Column L具有特定值的所有单元格，并返回与Column D找到的那些单元格在同一行中的值。
So far, I am only able to return one result, which would be the top most result in my list, but I want to find all the rest as well, which I don't know the code to use.
到目前为止，我只能返回一个结果，这将是我列表中最靠前的结果，但我也想找到其他所有结果，但我不知道要使用的代码。
Just to further explain: Value in cell D11is the value I want to find in Column L of sheet "Master List". Supposedly I find the value in cells L13, L15and L20, I want to return the value in cell D13, D15and D20into cells "C37:C39" of ws. Note: no. of cells that have the value may vary so the values returned will just appear from C37downwards (something like automatic multiple selection, copy and paste)
进一步解释一下：单元格D11中的值是我想在“主列表”表的 L 列中找到的值。假设我在单元格中找到值L13，L15并且L20，我想返回单元格中的值D13，D15并返回D20到单元格“ C37:C39”的 ws 中。注意：没有。具有该值的单元格数量可能会有所不同，因此返回的值只会从C37下方显示（类似于自动多选、复制和粘贴）
Here's a little something to start the ball rolling:
这里有一些东西可以开始滚动：
Sub FindRelatedProducts()
Dim cell As Excel.Range
Dim D11Value As Variant
Dim D11Row As Variant
Dim ws As Worksheet: Set ws = Sheets("RShip")
Set cell = ws.Range("D11")
D11Value = cell.Value
With Sheets("Master List")
D11Row = Application.Match(D11Value, .Range("L:L"), 0)
If Not IsError(D11Row) Then
ws.Range("C37") = .Range("D" & D11Row).Value
End If
End With
End Sub
回答by Arconath
Here's an example using range variables.
这是使用范围变量的示例。
You'll want to define a range for the input data range and a range for the output data. Then in the VBA you will want to change the wrk, inRngand outRngvariables to be the named ranges you defined and change the column indexes in the forand ifblocks to match the column index of the data you are looking for.
您需要定义输入数据范围和输出数据范围。然后在 VBA 中，您需要将wrk,inRng和outRng变量更改为您定义的命名范围，并更改for和if块中的列索引以匹配您要查找的数据的列索引。
Option Explicit
Option Base 1
Sub FindValues()
Dim wrk As Worksheet
Dim inRng As Range
Dim outRng As Range
Dim cntr As Long
Dim outCntr As Long
Dim findVal As Double
Set wrk = Worksheets("Data")
Set inRng = wrk.Range("LookupRange")
Set outRng = wrk.Range("OutputRange")
' Clear the output range in case you have fewer values on this run than on the previous one
outRng.ClearContents
' Set the value you are looking for
findVal = 1
' Iterate through the rows in the input range. If you find the result you want then write it to the output range
For cntr = 1 To inRng.Rows.Count
If inRng(cntr, 1) = findVal Then ' Assumes the value you are finding is in column 1 of the input range
outRng(outCntr, 1) = inRng(cntr, 2) ' Assumes the values you are exporting is in column 2 of the input range
outCntr = outCntr + 1
End If
Next cntr
End Sub
