CODEWITHSUNDEEP
vbaexcel
user2530250
user2530250

Reputation: 17

Find cells with same value within one column and return values from separate column of same row

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.

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

Upvotes: 0

Views: 9758

Answers (1)

Arconath
Arconath

Reputation: 453

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, 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

Upvotes: 1

Related Questions