Search multiple input from one workbook and replace value in another workbook

rsubra

New Member
Joined
Sep 17, 2018
Messages
1
Below is the data in my two different sheets:

Workbook1

LastNameFirstNameNewPermission
Aadhi

<tbody>
</tbody>
Vetrivel

<tbody>
</tbody>
Risk Quality Reviewer II

<tbody>
</tbody>
Abudhahir

<tbody>
</tbody>
Imrana Parveen

<tbody>
</tbody>
Risk Reviewer II

<tbody>
</tbody>
Achari

<tbody>
</tbody>
Deepthi

<tbody>
</tbody>
Risk Quality Reviewer II

<tbody>
</tbody>

<tbody>
</tbody>








Workbook2
LastName
FirstNameCurrentPermission
AadhiVetrivelRead Only
AbudhahirImrana ParveenRead Only
AchariDeepthiRead Only

<tbody>
</tbody>

What I want to achieve:

I want to be able to search Workbook2 based on LastName and FirstName column coming from Workbook1 and get the NewPermission value and then replace the value of CurrentPermission column in Workbook2 with that of the NewPermission value.

Please advice an easy and efficient way to achieve this as I have to do this for over 1000 rows on regular basis.

Thank you so much for all of your help in advance.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
This would be easier if you could temporarily add a helper column to Workbook2 which is a combination of last & first name,
Change all the red text to your actual references which you could SET ahead of time.

Code:
sub doreplacer
dim lastrow1 as long
dim lastrow2 as long
lastrow1 = [COLOR=#ff0000]workbook1[/COLOR].sheets("[COLOR=#ff0000]whateveryoursheetname[/COLOR]").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
lastrow2 = [COLOR=#FF0000]workbook2[/COLOR].sheets("[COLOR=#FF0000]whateveryoursheetname[/COLOR]").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
rng = [COLOR=#ff0000]combocol_on_workbook2 &"1:combocol_on_workbook2"[/COLOR] & lastrow2
for x = 1 to lastrow1
if iserror(application.match([COLOR=#ff0000]wbk1last&firstname[/COLOR], [COLOR=#FF0000]workbook2[/COLOR].sheets("[COLOR=#FF0000]whateveryoursheetname[/COLOR]").range(rng),0))=false then
hit = 0 'reset
hit = application.match([COLOR=#ff0000]wbk1last&firstname[/COLOR], [COLOR=#FF0000]workbook2[/COLOR].sheets("[COLOR=#FF0000]whateveryoursheetname[/COLOR]").range(rng),0))
if hit>0 then
[COLOR=#FF0000]workbook2[/COLOR].sheets("[COLOR=#FF0000]whateveryoursheetname[/COLOR]").cells(hit,[COLOR=#ff0000]CurrentPermissionColumn[/COLOR]) = [COLOR=#FF0000]workbook1[/COLOR].sheets("[COLOR=#FF0000]whateveryoursheetname[/COLOR]").Cells(x,[COLOR=#ff0000]NewPermissionColumn[/COLOR])
endif
endif
next x
msgbox "Complete"
end sub
 

Watch MrExcel Video

Forum statistics

Threads
1,090,040
Messages
5,412,008
Members
403,407
Latest member
craigey1

This Week's Hot Topics

Top