Loop Through Range to get values

[kiwikiki718]

I have a range of cells that I would like to compare and get the value if they match.

Range J3:J13 in worksheet 1


Example
If range J3:J13 in worksheet1 = P1 in worksheet2 I need to display K3 Value from worksheet 1 in cell P2 in worksheet2
If range J3:J13 in worksheet1 = Q1 in worksheet2 I need to display L3 Value from worksheet 1 in cell Q2 in worksheet2

If range J3:J13 in worksheet1 = R1 in worksheet2 I need to display K3 Value from worksheet 1 in cell R2 in worksheet2
If range J3:J13 in worksheet1 = S1 in worksheet2 I need to display L3 Value from worksheet 1 in cell R2 in worksheet2

Note

P and Q are a set
R and S are a set
T and U are a set and so on



basically I want to compare range J3:J13 in worksheet 1 to all of the values in the first row of worksheet 2 starting at P1 then Q1 etc. and display the value of either K or L in worksheet 2 depending on what is being compared on worked sheet 2.

I have a formula that I entered in each cell manually which was very time consuming and very flexible. I wanted to know if this could be automated using some sort of loop function.

 

[HaHoBe]

Hi kiwikiki718,

really

If  range J3:J13 in worksheet1  = S1 in worksheet2 I need to display L3 Value from worksheet 1 in cell R2 in worksheet2

Maybe this can be used as a start:

Public Sub MrE_1233098_1705109()
' https://www.mrexcel.com/board/threads/loop-through-range-to-get-values.1233098/
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lngCol As Long

Const cstrLastColumn As String = "U"

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

For lngCol = 16 To Cells(1, cstrLastColumn).Column
  If ws1.Range("J3").Value = ws2.Cells(1, lngCol).Value Then
    If WorksheetFunction.IsEven(lngCol) Then
      ws2.Cells(2, lngCol).Value = ws1.Range("K3").Value
    Else
      ws2.Cells(2, lngCol).Value = ws1.Range("L3").Value
    End If
    Exit For
  End If
Next lngCol

Set ws2 = Nothing
Set ws1 = Nothing
End Sub

Please adjust the sheetnames to suit and alter the constant for the last column to check. I shortened the range to compare only the first cell. If a check for all cells in the range to be of the same value please add that check or come back and tell here.

Ciao,
Holger