col D (in sheet1 called Students),
col A (in Sheet2 called Students too) and
col B (in sheet2 called Age). In
colD there are a lot of similar Students names that are sorted from A to Z.
I have this code that finds matched students name and put his age in
col D (sheet1):
Set rngSearch = Sheets("Sheet1").Range("D:D") For Each rngSearch In Drng Set rngFound = Arng.Find(What:=rngSearch, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) 'if studentname is found: If Not rngFound Is Nothing Then rngSearch.Offset(0, 0) = rngFound.Offset(0, 1) Else rngSearch.Offset(0, 0) = "-" End If Next
I want to loop
col D once and if exists a match students name in
colA (sheet2), I want to copy in
colD (sheet1) the B column (sheet2) that matches to
colD because I don't want to loop every similar students name. I mean: if a student name (for example: Andrew) in colD matches to a student name ('Andrew') in
colA, then copy
colB (matches to col A- for example Andrew has 15 years old) in
colD (sheet1). And if the string 'Andrew' is repeated in
colD (I found it in another cells), don't loop for it again
colB (sheet2), but copy the value from the first string.
colD: Students: row1: Andrew row2: Andrew row3: Andrew row4: Andrew row5: Andrew row6: Andrew row7: Ben row8: Ben row9: Edoardo row10: Helen row11: Leonardo row12: Leonardo row13: Robert rowN: ..
Students: colB: Age: row1: Michael 16 row2: Timoth 20 row3: Andrew 15 row4: Edoardo 19 row5: Ben 13 row6: Robert 24 row7: Helen 17
What I want:
colD: Students: row1: 15 row2: 15 row3: 15 row4: 15 row5: 15 row6: 15 row7: 13 row8: 13 row9: 19 row10: 17 row11: 0 row12: 0 row13: 24 rowN: ..
Can anyone help me to optimize this code, please?