Loop through a range to find a value. If found, return a value of same row but different column

Question

My Excel has two sheets: Sheet1 and Sheet2

Sheet1

Sheet2

Sheet1:

Employee
ID       Name       Surname     Category    Location Gender
1234    Emplo1  SurnameEmplo1   Director    Italy    Man
4321    Emplo2  SurnameEmplo2   Technician  Ireland  Woman
9876    Emplo3  SurnameEmplo3   Technician  Russia   Man
6789    Emplo4  SurnameEmplo4   Operator    Spain    Man
5678    Emplo5  SurnameEmplo5   Director    France   Woman
8765    Emplo6  SurnameEmplo6   Operator    Poland   Woman

Sheet2:

Employee
  ID    Category    Location

I would like my macro to loop through each cell in Column D looking for the value "Director".

If the value "Director" is found, I would like to fill columns in Sheet2, that is, values 1234 and 5678 in Column A (corresponding to Director row) and Italy and France in Column C, as well as "Director" in Column B.

Sorry about my poor code, I've just started in VBA and it does not work at all. Is the approach correct?

Sub LookUpDirector()

Dim rng As Range
Set rng = Sheets("Sheet1").Range("D2:D7")

On Error Resume Next

'Loop through the Range to find the value

 For Each cell In rng

'If value is found, insert values in Sheet2

   If cell.Value = "Director" Then
     Sheets("Sheet2").Range("B2").Value = cell.Value
     Sheets("Sheet2").Range("A2").Value = "=INDEX(Sheet1!$A$2:$A$7;MATCH(""Director"";Sheet1!$D$2:$D$7;0))"
     Sheets("Sheet2").Range("C2").Value = "=INDEX(Sheet1!$E$2:$E$7;MATCH(""Director"";Sheet1!$D$2:$D$7;0))"
   End If

 Next cell

End Sub

santavital santavital · Accepted Answer · 2020-02-03T11:31:59

1st search for VLOOKUP example; 2nd if you still want use macros try two for {i,j} and Cells[i,j]

Loop through a range to find a value. If found, return a value of same row but different column

1 Answers