查找一列中具有相同值的单元格，并从同一行的不同列中返回值 [英] Find cells with same value within one column and return values from separate column of same row

问题描述

我想在 L列中找到具有特定值的所有单元格，并返回与找到的那些单元格相同的行的 D列中的值.

I want to find all the cells in Column L with a particular value and return the values in Column D of the same row as those cells found.

到目前为止，我只能返回一个结果，这将是列表中最上面的结果，但是我也想找到所有其余的结果，我不知道要使用的代码.

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.

仅作进一步说明: D11 单元格中的值是我想在工作表主列表"的L列中找到的值.假设我在 L13 ， L15 和 L20 单元格中找到了值，我想在单元格 D13 中返回该值， D15 和 D20 放入ws的单元格" C37:C39 ".注意:不可以.的具有该值的单元格可能会有所不同，因此返回的值只会从 C37 向下显示(类似于自动多项选择，复制和粘贴)

Just to further explain: Value in cell D11 is the value I want to find in Column L of sheet "Master List". Supposedly I find the value in cells L13, L15 and L20, I want to return the value in cell D13, D15 and D20 into cells "C37:C39" of ws. Note: no. of cells that have the value may vary so the values returned will just appear from C37 downwards (something like automatic multiple selection, copy and paste)

这里有一些东西可以开始滚球了:

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

推荐答案

下面是使用范围变量的示例.

Here's an example using range variables.

您将要为输入数据范围定义一个范围，并为输出数据定义一个范围.然后在VBA中，您需要将 wrk ， inRng 和 outRng 变量更改为您定义的命名范围，并更改其中的列索引 for 和 if 块以匹配您要查找的数据的列索引.

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, inRng and outRng variables to be the named ranges you defined and change the column indexes in the for and if blocks to match the column index of the data you are looking for.

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

这篇关于查找一列中具有相同值的单元格，并从同一行的不同列中返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！