How to replace cell data based on criteria

[Closed]
Report
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hello,
I'm trying to write a macro in excel that will utilize two spreadsheets. One sheet contains a large table of information while the other only contains two columns. What I need is to find cells that match between say sheet1 column e and sheet2 column b. then based on that result either hide the row that does not have a match in sheet2 or replace say sheet1 d1 with data from sheet2 a1. then do this for the entirety of the large table. Thanks for all the help



1 reply

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Craig,

See if this code yields the desired result:
Sub test()
Dim lRow, lRow2, x, y As Integer

lRow = Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row
lRow2 = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row

For Each cell In Sheets("Sheet1").Range("E1:E" & lRow)
x = 1
y = 0

Do

If cell.Value = Sheets("Sheet2").Cells(x, 2).Value Then
Sheets("Sheet2").Cells(x, 1).Copy Destination:=cell.Offset(0, -1)
y = y + 1
End If

x = x + 1

Loop Until x > lRow2

If y = 0 Then cell.EntireRow.Hidden = True

Next

End Sub

Best regards,
Trowa